cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
datadonuts
Advocate I
Advocate I

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
dateweekdayweekdaycheckworking days
26/12/2020Sat0257
27/12/2020Sun0258
28/12/2020Mon1259
29/12/2020Tue1260
30/12/2020Wed1261
31/12/2020Thr1262
01/01/2021Fri11
02/01/2021Sat0 
03/01/2021Sun0 
04/01/2021Mon12
05/01/2021Tue13
 
 
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.

Cookbook page 56.jpg

 

 

Any idea is highly appreciated. @Greg_Deckler 

 

See you guys

Benedikt

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
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.

 


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
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.

 


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors