Weekly Shift wise Cummulative sum

[pra137]

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 

Date	Week	Shift	Value	Cummulative
1-1-2020	1	F	-10	-10
1-1-2020	1	S	-10	-20
1-1-2020	1	N	0	-20
2-1-2020	1	F	-10	-30
2-1-2020	1	S	-10	-40
2-1-2020	1	N	0	-40
6-1-2020	1	N	0	-40
7-1-2020	1	F	10	-30
7-1-2020	1	S	0	30
7-1-2020	1	N	-10	-20
8-1-2020	2	F	10	10
8-1-2020	2	S	10	20
8-1-2020	2	N	0	20

Thanks,
pra137

[Icey · Accepted solution]

Hi @pra137 ,

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.

[amitchandak]

@pra137 , 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]) ))

[pra137]

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

cumm

-10

-10

0

-20

-20

0