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

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

6 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    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] )
    )
)