Forum Discussion
Returning first working date per week
If you want a date for each row, then try
FirstWorkdayOfWeek = CALCULATE ( MIN ( 'Calendar'[Date] ), ALLEXCEPT ( 'Calendar', 'Calendar'[Year], 'Calendar'[Week of Year] ) )This takes the minimal date in the calendar table for the Year and Week of Year combination in that particular row.
Hello all,
Thank you very much for your replies. Please see below the outcome of all 4 replies. I decided to flag AlexisOlson as the preferred solution as I still try to put my mind around the "earlier" function that other users had used at their replies.
Super UserEARLIER can be a bit confusing if you think it refers to an earlier time when it actually refers to an earlier row context.
You can rewrite a version using EARLIER
CALCULATE (
MIN ( 'Calendar'[Date] ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Year_Week] = EARLIER ( 'Calendar'[Year_Week] )
)
)using a variable instead
VAR CurrYear_Week = 'Calendar'[Year_Week]
RETURN
CALCULATE (
MIN ( 'Calendar'[Date] ),
FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year_Week] = CurrYear_Week )
)thank you AlexisOlson for that . There is a very educative vide at Curbal youtube channel explaining earlier. However, I prefer the use of variables as , among other things , provides a clearer picture of how the measure works . Also, the SQLBI team is also in favour of using variables instead of earlier. Going back to your variable example, I believe that the variable reflects the current row, am i correct in reading it like that? thank you
- AlexisOlson
Yes. When rows context exists (like in a calculated column), 'Calendar'[Year_Week] refers to the Year_Week value in the current row.