HELP ON CUMULATIVE CALCULATION

[Anonymous]

hi, this is my monthly data ( Month as number 1, 2,3 .. )

Month
BLOCK	Global	Mgmt	1	2	3	4	5	6	7	8	9	10	11	12
INDIA	Overheads	Cola	-              0.28	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15
INDIA	Overheads	Pepsi	0.02	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
JAPAN	Overheads	Cola	-              0.07	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15
JAPAN	Overheads	Pepsi	-              0.02	0.06	0.06	0.06	0.06	0.06	0.06	0.06	0.06	0.06	0.06	0.06
USA	Under	Cola	-              0.00	0.09	0.09	0.09	0.09	0.09	0.09	0.09	0.09	0.09	0.09	0.09
USA	under	Lime	0.00	0.03	0.03	0.03	0.03	0.03	0.03	0.03	0.03	0.03	0.03	0.03

What I am struggling and my requiremnt  is..

If I select India, Overhead and Pepsi with

Month 1 : My value should come -0.28

Month 2 : -0.13

MOnth 3: 0.02

i.e Cummulative addition based on the Block , Global , Mgmt and month number filter.

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

After unpivoting your table, you could create a new column to show the data.

Column =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        'Table',
        'Table'[Attribute] <= EARLIER ( 'Table'[Attribute] )
            && 'Table'[Mgmt ] = EARLIER ( 'Table'[Mgmt ] )
            && 'Table'[BLOCK] = EARLIER ( 'Table'[BLOCK] )
    )
)

Here is my test file for your reference.

[Anonymous]

thank you all of you. This has resolved my problem.

[FrankAT]

Hi,

you can do it like this:

REgrads FrankAT

[Anonymous]

90% correct, I did the same way but why if I take the monthe number as a seperate slicer/filter and If I select 2, the output is not coming as -0.13 or If I select 3 the output is not coming as 0.02.

[FrankAT]

Hi @Anonymous,

try this:

or change the slicer properties like this:

Regards FrankAT

[amitchandak]

If you are using date the you can use following for cumulative

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))

If you have month No,

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all(Table),Table[Month] <=maxx(Table,Table[Month])))

That will work with year.

[Anonymous]

hi,

the output is not as expecetd if I select the filters like Block , Global and MGT. The cummulative is working fine based on the month no .

[amitchandak]

If you have date, prefer the formula In given with date using date calendar.  If you can create date using month and year do that.

If not, move month to another table and then use all(Month) in the filter (all(month), month[month]<=max(month[month]))