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)