Cumulative Weekday

[tanct]

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.

[Ashish_Mathur · Accepted 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.

[v-haibl-msft · Accepted solution]

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

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

[tanct]

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.

.

[Zubair_Muhammad]

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

[tanct]

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.

[Ashish_Mathur · Accepted 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.