Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Weekly Shift wise Cummulative sum

Hi Guys,

I have to calculate shiftwise cummulative of weekly data, In my table 1day of week contains 3 shifts i.e in 1 week there are 21 shifts ,
when the new week start cummulative should be start from 1 days and F shift of that Week . i want result like below cummulative column please help 

DateWeekShiftValueCummulative
1-1-20201F-10-10
1-1-20201S-10-20
1-1-20201N0-20
2-1-20201F-10-30
2-1-20201S-10-40
2-1-20201N0-40
6-1-20201N0-40
7-1-20201F10-30
7-1-20201S030
7-1-20201N-10-20
8-1-20202F1010
8-1-20202S1020
8-1-20202N020
 


Thanks,
pra137



Show and Tell
Tips and Tricks
  • Icey's avatar
    Icey
    6 years ago

    Hi Anonymous ,

     

    Try this:

     

    1. Add Index column in Power Query Editor.

     

    2. Create a calculated column or measure like so:

    Column = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        'Table',
        'Table'[Week] = EARLIER ( 'Table'[Week] )
            && 'Table'[Index] <= EARLIER ( 'Table'[Index] )
    )
)

     

    Measure = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALLSELECTED( 'Table' ),
        'Table'[Week] = MAX( 'Table'[Week] )
            && 'Table'[Index] <= MAX( 'Table'[Index] )
    )
)

    BTW, .pbix file attached.

     

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , try like

    Cumm = CALCULATE(SUM(Table[Value]),filter(all(Table),Table[date] <=max(Table[Date]) && Table[Shift] =max(Table[Shift]) ))

    or

    Cumm = CALCULATE(SUM(Table[Value]),filter(allselected(Table),Table[date] <=max(Table[Date]) && Table[Shift] =max(Table[Shift]) ))

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hii amitchandak ,
      Thanks for reply but it is not working it gives me output like Cumm column

      cumm
      -10
      -10
      0
      -20
      -20
      0

       

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Try this:

     

    1. Add Index column in Power Query Editor.

     

    2. Create a calculated column or measure like so:

    Column = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        'Table',
        'Table'[Week] = EARLIER ( 'Table'[Week] )
            && 'Table'[Index] <= EARLIER ( 'Table'[Index] )
    )
)

     

    Measure = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALLSELECTED( 'Table' ),
        'Table'[Week] = MAX( 'Table'[Week] )
            && 'Table'[Index] <= MAX( 'Table'[Index] )
    )
)

    BTW, .pbix file attached.

     

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.