DAX Cookbook "Determine day and working day numbers"

[datadonuts]

Hi everyone, hi Greg,

I got your book "Dax cookbook" and am going to go through it, all 520 pages (great book, love it! I failed on the Italian's "Definitive DAX Guide"). I am not totally new with PBI and DAX, around 2 years of experience, but complex DAX is still hard stuff for me, so as this challenge.

I went to the mentioned chapter (page 55), got it going but I am not sure about the result. I would expect the outcome should be the count of ONLY the working days of a calendar year, ignoring Sat and Sun (means weekday([date],2) <6), bc I am from Germany), which totals to 262 days in 2020. Then the calculation should repeat for 2021 giving the number of workdays for that year and so on. I realized the first part with the following DAX. 

_Workdaycheck = if(WEEKDAY(R04_Calendar[Date],2) <6,1,0)

_Workdayearlier = SUMX(FILTER(R04_Calendar,'R04_Calendar'[Date]<=EARLIER('R04_Calendar'[Date])),R04_Calendar[_Workdaycheck])

This gives me the count of the working days ignoring Sat and Sun in the calendar and works perfectly for one year e.g. I filter the table to 2020. But I could not solve the problem, that it keeps counting over the 31/12/2020, while it actually should restart counting on Jan 1st.

Expected result

date	weekday	weekdaycheck	working days
26/12/2020	Sat	0	257
27/12/2020	Sun	0	258
28/12/2020	Mon	1	259
29/12/2020	Tue	1	260
30/12/2020	Wed	1	261
31/12/2020	Thr	1	262
01/01/2021	Fri	1	1
02/01/2021	Sat	0
03/01/2021	Sun	0
04/01/2021	Mon	1	2
05/01/2021	Tue	1	3

The result in the book looks much different, so I am wondering, whether I am doing something wrong and how I can solve the problem to restart the counting cycle.

Any idea is highly appreciated. @Greg_Deckler 

See you guys

Benedikt

[Greg_Deckler · Accepted solution]

@datadonuts The calculation in the book creates a sequential day number similar to a sequential week, making workday over workday or week over week comparisons very easy. If you want it to reset at the beginning of each year, do this:

_Workdayearlier = SUMX(FILTER(R04_Calendar,'R04_Calendar'[Date]<=EARLIER('R04_Calendar'[Date]) && 'R04_Calendar'[Year] = EARLIER('R04_Calendar'[Year])),R04_Calendar[_Workdaycheck])

Also, if you don't want to use EARLIER, you can do this as an equivalent:

_Workdayearlier = 
  VAR __Year = [Year]
  VAR __Date = [Date]
RETURN
  SUMX(FILTER(R04_Calendar,'R04_Calendar'[Date]<=__Date && 'R04_Calendar'[Year] = __Year ),R04_Calendar[_Workdaycheck])

Glad you like the book. It is my personal favorite of the books I have written.

[datadonuts]

Most of the time solution is easier than thought. Thanks a lot!