Assign to the first work day the value of WD1, second working day = WD2..

[Nun]

Hello,

I created a calendar table 

calendar = CALENDAR(DATE(2021,01,02),DATE(2021,12,31))

and columns:

WeekN = WEEKNUM([Date])

IsWorkingDay = IF(WEEKDAY('Calendar'[Date],2)>5,0,IF('calendar'[Date] = DATE(2021,04,02) || 'calendar'[Date] = DATE(2021,04,02),0,1))

but I need to assign to the first working day of the month (for all the months) the value WD1, to the second the value of WD2.

so for example, in January 2021 the first working day is 04.01.2021 (which is Monday) and I need to assign WD1, in April the first working (to be assigned WD1) is 01.04.2021 (which is Friday) and the second working day (to be assigned WD2) is 05.04.2021 (which is Tue.)

Thanks a lot for supporting!

[amitchandak]

@Nun , I think you need work day number of month, see if these columns can help

Work Day = if(WEEKDAY([Date],2)>=6,0,1)
Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])

Work day of month = Sumx(filter(Date, [Month year] = earlier([Month year])),[Work Day])
Work day of week = Sumx(filter(Date, [Weekyear] = earlier([Weekyear])),[Work Day])

[Nun]

@amitchandak , thanks for supporting.

I checked a previous post from you 🙂 which is high appreciated, I created the columns described

but how I can assign "WD1" to the first working day (in January is 04.01.2021 ((Monday)), in April is 01.04.201 (Thu), as well WD2..?

Thank you in advance!

[v-rzhou-msft · Accepted solution]

Hi @Nun 

Add a YearMonth column when you create WEEKDAY table.

WEEKDAY = ADDCOLUMNS(CALENDAR(DATE(2021,01,02),DATE(2021,12,31)),"YearMonth",YEAR([Date])*100+MONTH([Date]))

Then build try my code.

Flag = 
VAR _WeekDay = WEEKDAY('WEEKDAY'[Date],2)
VAR _Rank = IF(_WeekDay in {6,7},BLANK(),1)
RETURN
_Rank

WD = 
VAR _WeekDay = WEEKDAY('WEEKDAY'[Date],2)
VAR _Result = IF(_WeekDay in {6,7},BLANK(),"WD"&""&CALCULATE(SUM('WEEKDAY'[Flag]),FILTER('WEEKDAY','WEEKDAY'[YearMonth] = EARLIER('WEEKDAY'[YearMonth])&&'WEEKDAY'[Date]<=EARLIER('WEEKDAY'[Date]))))
RETURN
_Result

Result:

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Nun]

Thanks a lot!!!