Forum Discussion
bryanrendra
Helper II
Helper IICumulative total with multiple item and time
I have been googling and still cant find the best way to generate updated price value. I am trying to find updated price value that coming from original price + running total incremental price in gi...
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]) ))
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
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
UpdatedPrice
This 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 |