Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Returning first working date per week

Hello Community,   Hope you are well.   I have the below calendar table. I need to add a calculated column returning the first working date per week.   Any help more than welcomed.   DateYear...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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.

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

EARLIER 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 )
    )
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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