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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
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
Employee
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] )
    )
)

Regards
Zubair

Please try my custom visuals

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] )
    )
)

 

 


Regards
Zubair

Please try my custom visuals

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.