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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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. 

 

 

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous 

    for a calculated column you have a row context. For each row (ech cell of this calculated coulumn), FILTER will iterate over the table creating a new row context inside the earlier one. EARLIER restores the previous raw context and evaluates the expression based on it. 
    In your caseEARLIER ( 'Calendar'[Working/Weekend] ) is actually the value of this coulmn at the same row under evaluation. While 'Calendar'[Working/Weekend] will have different values during the iteration of FILTER

  • 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

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

        Yes. When rows context exists (like in a calculated column), 'Calendar'[Year_Week] refers to the Year_Week value in the current row.