# Most Recent Value for Every Date

This is probably simple for you seasoned DAX warriors out there, but it’s not clicking in my novice brain today…

I have attached a PBIX file with a simplified version of my problem. I have two tables:

• Dates (my Dates table)
• Hours ('hours worked" reported on the first of each month for three locations)

I also have two measures:

• Hours YTD (total hours worked YTD for selected location and week)
``````Hours YTD =
CALCULATE(
SUM(Hours[Hours]),
DATESBETWEEN(Dates[Date],
STARTOFYEAR(Dates[Date]),
MAX(Dates[Date])
)
)
``````
• 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.

Any ideas? Thanks!

Test Case 7.pbix (92.1 KB)

Hi @DaveC,

Can you give this a go, thanks.

``````Most recent week hours <> blank =
VAR LastNonBlankWeek =
CALCULATE( MAX( Dates[ISO Weeknumber] ),
FILTER(
FILTER( ALL( Dates ), Dates[ISO Weeknumber] <= MAX( Dates[ISO Weeknumber] ) ),
NOT( ISBLANK( [Most Recent Week with Reported Hours] ) ) && [Most Recent Week with Reported Hours] <> 0
))
RETURN

CALCULATE( [Most Recent Week with Reported Hours],
FILTER( ALL(Dates), Dates[ISO Weeknumber] = LastNonBlankWeek )
)
``````

1 Like

It worked perfectly, @Melissa. Thank you!

See if this works for you:

``````Most Recent Week No Blank =

VAR SelHrsYTD = [Hours YTD]

VAR Fill =
CALCULATE(
MIN( Dates[ISO Weeknumber]),
FILTER(
ALL(Dates),
[Hours YTD] = SelHrsYTD
)
)

RETURN Fill
``````

• Brian

Just saw @Melissa beat me to it while I was posting. Two solutions for the price of one today…

• Brian

Thanks, @BrianJ! Yours worked perfectly also!

Hi @DaveC

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.

@DaveC Thanks for clarification

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.

‘OtherSolution’ =
VAR MaxDate = MAXX (
FILTER ( ALLSELECTED ( Hours ), Hours[Date] < = MAX ( Dates[Date] ) ),
Hours[Date]
)
RETURN
LOOKUPVALUE ( Dates[ISO Weeknumber], Dates[Date], MaxDate )

Best,
DJ

Hi @Melissa are you able to break this formula down into steps so I can understand what is really going on

step 1

FILTER( ALL( Dates ), Dates[ISO Weeknumber] <= MAX( Dates[ISO Weeknumber] ) )

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?

Step 2

FILTER(
Step 1,
NOT( ISBLANK( [Most Recent Week with Reported Hours] ) ) && [Most Recent Week with Reported Hours] <> 0

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.

Thanks much appreciated

Patrick

Hi Patrick,

Let’s examine what is going on

1. 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)
2. 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.

1. Finally we CALCULATE [Most Recent Week with Reported Hours] for the LastNonBlankWeek