Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be 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

Reply
tanct
Regular Visitor

Cumulative Weekday

 

 

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.2017-11-06_15-33-05.png

2 ACCEPTED SOLUTIONS

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

v-haibl-msft
Microsoft Employee
Microsoft Employee

@tanct

 

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

View solution in original post

6 REPLIES 6
v-haibl-msft
Microsoft Employee
Microsoft Employee

@tanct

 

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

Zubair_Muhammad
Community Champion
Community Champion

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.

 

.10.png

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.

2017-11-07_11-49-48.png

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.