March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Dear All,
Could you please share what is the Dax code for the cumulative working day for the following condition below:-
1. First working day of the month assigned as 1, then cumulative adding the weekday (without weekend is considered) during the month (i.e Dec'17 having 21 working days, with 1 to 3 Dec'17= 1, 4 Dec'17 =2, etc., total working days for Dec'17 = 21)
Thanks in advance.
Solved! Go to Solution.
Hi @tanct,
Try this calculated column formula
=CALCULATE(COUNTROWS('DIM-Calendar'),FILTER('DIM-Calendar','DIM-Calendar'[Date]<=EARLIER('DIM-Calendar'[Date])&&'DIM-Calendar'[Date]>=EARLIER([Date])-DAY(EARLIER([Date]))+1&&'DIM-Calendar'[Weekend]=1))
Hope this helps.
You can also try to create a calculated column with following formula.
Cumulative Weekday = CALCULATE ( SUM ( 'DIM-Calendar'[Weekend] ), FILTER ( 'DIM-Calendar', 'DIM-Calendar'[Date] <= EARLIER ( 'DIM-Calendar'[Date] ) && 'DIM-Calendar'[Year] = EARLIER ( 'DIM-Calendar'[Year] ) && 'DIM-Calendar'[Month] = EARLIER ( 'DIM-Calendar'[Month] ) ) )
Best Regards,
Herbert
You can also try to create a calculated column with following formula.
Cumulative Weekday = CALCULATE ( SUM ( 'DIM-Calendar'[Weekend] ), FILTER ( 'DIM-Calendar', 'DIM-Calendar'[Date] <= EARLIER ( 'DIM-Calendar'[Date] ) && 'DIM-Calendar'[Year] = EARLIER ( 'DIM-Calendar'[Year] ) && 'DIM-Calendar'[Month] = EARLIER ( 'DIM-Calendar'[Month] ) ) )
Best Regards,
Herbert
Hi @tanct
Add this calculated column for Cumulative Working Days
= CALCULATE ( SUM ( 'Dim-Calendar'[Weekend] ), FILTER ( 'Dim-Calendar', 'Dim-Calendar'[Day] <= EARLIER ( 'Dim-Calendar'[Day] ) && 'Dim-Calendar'[Month] = EARLIER ( 'Dim-Calendar'[Month] ) ) )
Dear Zubair,
It seems not working.
03.07.2016 should be = 1 and 04.07.2016 = 3 (instead of 2 and 4), the weekend seem unable to adding up correctly.
Thanks for your advice again.
.
Hi @tanct
Shouldn't 04.07.2016 be = 2 (second working day)
Could you share file via onedrive or google drive?
Give this code a try for the time being
= CALCULATE ( SUM ( 'Dim-Calendar'[Weekend] ), FILTER ( 'Dim-Calendar', 'Dim-Calendar'[Day] <= EARLIER ( 'Dim-Calendar'[Day] ) && 'Dim-Calendar'[Month] = EARLIER ( 'Dim-Calendar'[Month] ) && 'Dim-Calendar'[Month] = EARLIER ( 'Dim-Calendar'[Month] ) ) )
Hi,
Thanks, please find the below link for the PBIX:-
https://drive.google.com/open?id=1V4bOPU8VHJ8B3C_GE9NQlyKpqEX7Md09
You may go to table DIM Calendar.
Basically, I have identified the weekend for each of the month (in column Weekend), just pending to sum up the computed weekend figure to arrive working day of the month. Thanks in advance.
Hi @tanct,
Try this calculated column formula
=CALCULATE(COUNTROWS('DIM-Calendar'),FILTER('DIM-Calendar','DIM-Calendar'[Date]<=EARLIER('DIM-Calendar'[Date])&&'DIM-Calendar'[Date]>=EARLIER([Date])-DAY(EARLIER([Date]))+1&&'DIM-Calendar'[Weekend]=1))
Hope this helps.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
123 | |
85 | |
85 | |
70 | |
51 |
User | Count |
---|---|
205 | |
153 | |
97 | |
79 | |
69 |