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

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.