Forum Discussion
Cumulative total with multiple item and time
- 6 years ago
bryanrendra , Try one of the two
DropPrice = 'Table'[Original Price]+CALCULATE(SUM('table'[IncrementalPrice]),
filter(
ALLSELECTED('table'),'table'[Date]<= max('table'[Date]) && 'table'[item] max(='table'[Date])))
DropPrice = 'Table'[Original Price]+CALCULATE(SUM('table'[IncrementalPrice]),
filter(
ALLSELECTED('table'),'table'[Date]<= max('table'[Date]) )) - Anonymous6 years ago
I have copied your sample data as a new table named "Pricing"
Table Name : Pricing
Item Original Price Date Incremental Price Pencil 3 01-Jan-19 Pencil 3 02-Jan-19 0.8 Pencil 3 03-Jan-19 Pencil 3 04-Jan-19 0.2 Pencil 3 05-Jan-19 0.3 Pencil 3 06-Jan-19 Book 5 01-Jan-19 1 Book 5 02-Jan-19 Book 5 03-Jan-19 Book 5 04-Jan-19 3 Book 5 05-Jan-19 Added the following Calculated Column
Updated Price = VAR OriginalPrice = Pricing[Original Price] VAR CurrentItem = Pricing[Item] VAR CurrentDate = Pricing[Date] VAR CumulativePriceChanges = SUMX ( FILTER ( ALLSELECTED ( Pricing ), Pricing[Item] = CurrentItem && Pricing[Date] <= CurrentDate ), Pricing[Incremental Price] ) VAR UpdatedPrice = OriginalPrice + CumulativePriceChanges RETURN UpdatedPriceThis gave me the following Result.
Item Original Price Date Incremental Price Updated Price Pencil 3 01-Jan-19 3 Pencil 3 02-Jan-19 0.8 3.8 Pencil 3 03-Jan-19 3.8 Pencil 3 04-Jan-19 0.2 4 Pencil 3 05-Jan-19 0.3 4.3 Pencil 3 06-Jan-19 4.3 Book 5 01-Jan-19 1 6 Book 5 02-Jan-19 6 Book 5 03-Jan-19 6 Book 5 04-Jan-19 3 9 Book 5 05-Jan-19 9
bryanrendra , Try one of the two
DropPrice = 'Table'[Original Price]+CALCULATE(SUM('table'[IncrementalPrice]),
filter(
ALLSELECTED('table'),'table'[Date]<= max('table'[Date]) && 'table'[item] max(='table'[Date])))
DropPrice = 'Table'[Original Price]+CALCULATE(SUM('table'[IncrementalPrice]),
filter(
ALLSELECTED('table'),'table'[Date]<= max('table'[Date]) ))
- bryanrendra6 years ago
Helper II
It works!, my problem was the original price and incremental price were at two different table with their own dates column. Now I have finally manage to create dimension date and the formula works. Thank you!