Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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 |
Solved! Go to 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 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.
Most of the time solution is easier than thought. Thanks a lot!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |