Returning first working date per week

[Anonymous]

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

[Anonymous]

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. 

[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 )
    )

[Anonymous]

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.

[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

[Anonymous]

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.

[AlexisOlson · Accepted solution]

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.

[tamerj1]

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] )
)

[amitchandak]

@Anonymous ,

NEw column =

if( [Date] =minx(filter(table, [Year_Week] = earlier([Year_Week]) ),[Date]) , True(), false())