Forum Discussion
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.
DateYearQuarterMonthWeek of YearYear_QuarterYear_MonthYear_WeekDay_NameWorking/Weekend_Day
| 01/01/2021 | 2021 | 1 | 1 | 1 | 2021.Q1 | 2021.M01 | 2021.W01 | Fri | Working |
| 02/01/2021 | 2021 | 1 | 1 | 1 | 2021.Q1 | 2021.M01 | 2021.W01 | Sat | Weekend |
| 03/01/2021 | 2021 | 1 | 1 | 1 | 2021.Q1 | 2021.M01 | 2021.W01 | Sun | Weekend |
| 04/01/2021 | 2021 | 1 | 1 | 2 | 2021.Q1 | 2021.M01 | 2021.W02 | Mon | Working |
| 05/01/2021 | 2021 | 1 | 1 | 2 | 2021.Q1 | 2021.M01 | 2021.W02 | Tue | Working |
| 06/01/2021 | 2021 | 1 | 1 | 2 | 2021.Q1 | 2021.M01 | 2021.W02 | Wed | Working |
| 07/01/2021 | 2021 | 1 | 1 | 2 | 2021.Q1 | 2021.M01 | 2021.W02 | Thu | Working |
| 08/01/2021 | 2021 | 1 | 1 | 2 | 2021.Q1 | 2021.M01 | 2021.W02 | Fri | Working |
| 09/01/2021 | 2021 | 1 | 1 | 2 | 2021.Q1 | 2021.M01 | 2021.W02 | Sat | Weekend |
| 10/01/2021 | 2021 | 1 | 1 | 2 | 2021.Q1 | 2021.M01 | 2021.W02 | Sun | Weekend |
| 11/01/2021 | 2021 | 1 | 1 | 3 | 2021.Q1 | 2021.M01 | 2021.W03 | Mon | Working |
| 12/01/2021 | 2021 | 1 | 1 | 3 | 2021.Q1 | 2021.M01 | 2021.W03 | Tue | Working |
| 13/01/2021 | 2021 | 1 | 1 | 3 | 2021.Q1 | 2021.M01 | 2021.W03 | Wed | Working |
| 14/01/2021 | 2021 | 1 | 1 | 3 | 2021.Q1 | 2021.M01 | 2021.W03 | Thu | Working |
| 15/01/2021 | 2021 | 1 | 1 | 3 | 2021.Q1 | 2021.M01 | 2021.W03 | Fri | Working |
| 16/01/2021 | 2021 | 1 | 1 | 3 | 2021.Q1 | 2021.M01 | 2021.W03 | Sat | Weekend |
| 17/01/2021 | 2021 | 1 | 1 | 3 | 2021.Q1 | 2021.M01 | 2021.W03 | Sun | Weekend |
| 18/01/2021 | 2021 | 1 | 1 | 4 | 2021.Q1 | 2021.M01 | 2021.W04 | Mon | Working |
| 19/01/2021 | 2021 | 1 | 1 | 4 | 2021.Q1 | 2021.M01 | 2021.W04 | Tue | Working |
| 20/01/2021 | 2021 | 1 | 1 | 4 | 2021.Q1 | 2021.M01 | 2021.W04 | Wed | Working |
| 21/01/2021 | 2021 | 1 | 1 | 4 | 2021.Q1 | 2021.M01 | 2021.W04 | Thu | Working |
| 22/01/2021 | 2021 | 1 | 1 | 4 | 2021.Q1 | 2021.M01 | 2021.W04 | Fri | Working |
| 23/01/2021 | 2021 | 1 | 1 | 4 | 2021.Q1 | 2021.M01 | 2021.W04 | Sat | Weekend |
| 24/01/2021 | 2021 | 1 | 1 | 4 | 2021.Q1 | 2021.M01 | 2021.W04 | Sun | Weekend |
| 25/01/2021 | 2021 | 1 | 1 | 5 | 2021.Q1 | 2021.M01 | 2021.W05 | Mon | Working |
| 26/01/2021 | 2021 | 1 | 1 | 5 | 2021.Q1 | 2021.M01 | 2021.W05 | Tue | Working |
| 27/01/2021 | 2021 | 1 | 1 | 5 | 2021.Q1 | 2021.M01 | 2021.W05 | Wed | Working |
| 28/01/2021 | 2021 | 1 | 1 | 5 | 2021.Q1 | 2021.M01 | 2021.W05 | Thu | Working |
| 29/01/2021 | 2021 | 1 | 1 | 5 | 2021.Q1 | 2021.M01 | 2021.W05 | Fri | Working |
| 30/01/2021 | 2021 | 1 | 1 | 5 | 2021.Q1 | 2021.M01 | 2021.W05 | Sat | Weekend |
| 31/01/2021 | 2021 | 1 | 1 | 5 | 2021.Q1 | 2021.M01 | 2021.W05 | Sun | Weekend |
| 01/02/2021 | 2021 | 1 | 2 | 6 | 2021.Q1 | 2021.M02 | 2021.W06 | Mon | Working |
Thank you in advance,
George
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.
9 Replies
- AlexisOlson
Super User
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.
- amitchandak
Anonymous ,
NEw column =
if( [Date] =minx(filter(table, [Year_Week] = earlier([Year_Week]) ),[Date]) , True(), false())
- tamerj1
Community Champion
Hi Anonymous
Try
MyColumn =
CALCULATE (
VALUES ( 'Calendar'[Working/Weekend] ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Year-Week] = EARLIER ( 'Calendar'[Year-Week] )
&& 'Calendar'[Working/Weekend] = EARLIER ( 'Calendar'[Working/Weekend] )
),
'Calendar'[Date] = MIN ( 'Calendar'[Date] )
) Hi Anonymous ,
Please try:
Column = CALCULATE(FIRSTNONBLANK('Table'[Date],TRUE()),FILTER('Table',[Year_Week]=EARLIER('Table'[Year_Week]) && [Working/Weekend_Day]="Working"))Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.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
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
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 ) )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
