Cumulative Total Based on Plant, Material, and Date

[Anonymous]

Hi All,

Iam stuck with some calculation of arriving cumulative total.

The following is the data i have.

Plant Id	Material	Req/Availbility Date	Opening Qty	Qty Utilized
1110	400269	31-05-2022	10,375	219
1110	400269	24-06-2022	10,375	-576
1110	400269	24-06-2022	10,375	-856
1110	400269	24-06-2022	10,375	-960
1110	400269	01-07-2022	10,375	-128
1110	400269	01-07-2022	10,375	-1,024
1111	400269	31-05-2022	50,000	-8
1111	400269	24-06-2022	50,000	-4,000
1111	400269	24-06-2022	50,000	1,000
1111	400269	24-06-2022	50,000	-340
1111	400269	01-07-2022	50,000	-4,577
1111	400269	01-07-2022	50,000	434

I need to calculate the cumulative (measure or column) based on the Plant, Material, and Date.

For example for Plant 1110 and material 40269 the least date is 31-05-2022 and opening inventory is 10,375 and utilized is 219. So for this line my cumulative is 10375 + 219 = 10594.

Next cumulative should be 10594 + (-596) = 10,018 and so on.

When the next combination ofh Plant and MAterial starts i.e., Plant 1111 and material 4000269 againfirst calucaltin should be of the least date 31-05-2022 and cumulative total is 50000 + (-8) = 49992 and second calcualation is 49992 + (-4000) = 45992.

The following is the sample result for your reference.

Plant Id	Material	Req/Availbility Date	Opening Qty	Qty Utilized	Cumulative
1110	400269	31-05-2022	10,375	219	10,594
1110	400269	24-06-2022	10,375	-576	10,018
1110	400269	24-06-2022	10,375	-856	9,162
1110	400269	24-06-2022	10,375	-960	8,202
1110	400269	01-07-2022	10,375	-128	8,074
1110	400269	01-07-2022	10,375	-1,024	7,050
1111	400269	31-05-2022	50,000	-8	49,992
1111	400269	24-06-2022	50,000	-4,000	45,992
1111	400269	24-06-2022	50,000	1,000	46,992
1111	400269	24-06-2022	50,000	-340	46,652
1111	400269	01-07-2022	50,000	-4,577	42,075
1111	400269	01-07-2022	50,000	434	42,509

Please suggest me a way to create either measure or column.

@amitchandak  @Ashish_Mathur @ChandeepChhabra @Fowmy  @JihwanKim @Jihwan_Kim 

[Anonymous]

@amitchandak Thanks this worked

[SolomonovAnton]

hello

first of all you should create Calendar table (_Calendar = Calendarauto())

create link between tables  '_Calendar'[date] and 'Table'[Req/Availbility Date]
after it create measure

TotalYTD = 
var _openQTY = min('Table'[ Opening Qty ])
var _YTD = TOTALYTD(SUM('Table'[ Qty Utilized ]),_Calendar[Date])
var _result = _openQTY+_YTD
return _result

then you can get result as bellow 

If I answered at you question please mark topic as resolved 🙂

[amitchandak · Accepted solution]

@Anonymous , Assuming Opening Qty is in every row

a new column =

[Opening Qty] + sumx(filter(Table, [Plant] = earlier([Plant])  && [Material] = Earlier([Material]) && [Availbility  Date] <= earlier([Availbility  Date]) ),[Qty Utilized])

a new measure

Max(Table[Opening Qty]) + sumx(filter(allselected(Table), [Plant] = max([Plant]) && [Material] = max([Material]) && [Availbility Date] <= max([Availbility Date]) ),[Qty Utilized])