Most Recent Week with Reported Hours (given a selected week and location, the week number of the most recent week in which hours were reported)
Most Recent Week with Reported Hours = WEEKNUM(MAX(Hours[Date]))
The screenshot below is what I get when I put it all together in a table:
What I need is for there to be a value in every row in the “Most Recent Week with Reported Hours” column. Weeks 1-4 should return “1”, weeks 5-9 should return “5”, etc.
Most Recent Week No Blank =
VAR SelHrsYTD = [Hours YTD]
VAR Fill =
CALCULATE(
MIN( Dates[ISO Weeknumber]),
FILTER(
ALL(Dates),
[Hours YTD] = SelHrsYTD
)
)
RETURN Fill
Both the solutions are giving different results.
I just want to understand what exactly your requirement is…
I’m attaching my solution based on my understanding.
@Rajesh, you’re very observant! The discrepancy in Week 9 was caused by my “Hours YTD” measure. Once I updated it so that the WEEKNUM() function is calculating based on ISO week numbers (by adding “21” as a second parameter to the WEEKNUM() function), the result is consistent for all solutions.
The discrepancy starting in Week 23 is because your solution (which I haven’t seen yet) is assuming that hours are reported on the first of the month. In fact, hours for the previous month are not reported until as late as the 10th day of the next month, so the ideal solution should not update the week number unless there is actual data for a given date in the Hours table. In the sample data I supplied, the last date for which there were hours reported was 5/1/2020 which is week number 18 (I think).
I hope that clarifies what I was trying to achieve, and why there were some discrepancies in the solutions.
Hi @DaveC
Simple and nice problem. I think using the reporting date could be a good idea because for each reporting date there is only one week’s number.
filter function simulates the row context and iterates row by row through the whole dates table (ALL function removes the initial filter context from the visual) and then checks for each row whether the Dates[ISO Weeknumber] is less than or equal to the MAX(Dates[ISO Weeknumber]). **I just get confused here with the MAX function. What is the MAX? Is the MAX from the visuals? So for example for the month of February 2020 is the MAX(Dates[ISO Weeknumber]) is 9?
The next Filter that is where my brain explodes. I am not sure what is happening there. My circuits are fried and I can’t think anymore what is happening. Can you please explain what is happening in this bit
NOT( ISBLANK( [Most Recent Week with Reported Hours] ) ) && [Most Recent Week with Reported Hours] <> 0
I get what the functions is doing but I cannot explain it or understand how to explain what is happening. Also can you make reference to row context, filter context and context transition in your explanation. That will really be helpful. I have to say DAX is not as easy as people claim. It looks easy but it is not that easy to apply or interpret.
We start with ALL( Dates ) removing the external filter context
in FILTER we keep only ISO Weekno’s smaller and/or equal to the ISO Weeknumber on the current row in the tabel/matrix (using MAX in the external context)
In the second FILTER we keep only ISO Weekno’s (from step 1) that don’t return a 0 or BLANK value for the measure [Most Recent Week with Reported Hours]
From these remaining Dates we identify the MAX ISO Weekno inside CALCULATE and retain that value as a variable called LastNonBlankWeek this is a scalar value.
Finally we CALCULATE [Most Recent Week with Reported Hours] for the LastNonBlankWeek
