Forum Discussion

tanct's avatar
tanct
Regular Visitor
8 years ago
Solved

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 ad...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    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's avatar
    v-haibl-msft
    8 years ago

    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's avatar
Zubair_Muhammad
Community Champion
8 years ago

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's avatar
tanct
Regular Visitor
8 years ago

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's avatar
    Ashish_Mathur
    Super User
    8 years ago

    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.