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