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

 

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

    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'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.