cancel
Showing results for
Did you mean:

## DAX Cookbook "Determine day and working day numbers"

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

1 ACCEPTED SOLUTION
Super User

@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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition
2 REPLIES 2
Super User

@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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition

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

Announcements