cancel
Showing results for
Did you mean:
Highlighted
Helper II

## Cumulative 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 given period of time (incrementalPrice is given monthly).

Here is formula:

DropPrice = 'Table'[Original Price]+CALCULATE(SUM('table'[IncrementalPrice]),
filter(
ALLSELECTED('table'),'table'[Date]<='table'[Date]))

 Item Original Price Date Incremental Price Updated Price logic Pencil \$                       3.00 1/1/2019 \$                       3.00 >Ori + Incr1 Pencil \$                       3.00 2/1/2019 0.8 \$                       3.80 >Ori + Incr1 +Incr2 Pencil \$                       3.00 3/1/2019 \$                       3.80 >Ori + Incr1+Incr2+Incr3 Pencil \$                       3.00 4/1/2019 0.2 \$                       4.00 So on.. Pencil \$                       3.00 5/1/2019 0.3 \$                       4.30 Pencil \$                       3.00 6/1/2019 \$                       4.30 Book \$                       5.00 1/1/2019 1 \$                       6.00 5+1 Book \$                       5.00 2/1/2019 \$                       6.00 Book \$                       5.00 3/1/2019 \$                       6.00 Book \$                       5.00 4/1/2019 3 \$                       9.00 5+3+1 Book \$                       5.00 5/1/2019 \$                       9.00
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IX

## Re: Cumulative total with multiple item and time

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

Proud to be a Super User!

Highlighted
Solution Sage

## Re: Cumulative total with multiple item and time

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

``````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

4 REPLIES 4
Highlighted
Super User IX

## Re: Cumulative total with multiple item and time

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

Proud to be a Super User!

Highlighted
Solution Sage

## Re: Cumulative total with multiple item and time

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

``````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

Highlighted
Helper II

## Re: Cumulative total with multiple item and time

Thank you so much, its working 100%. However, is there any way to do it using measure? considering that I have quite large data set and I am trying to save some memory.

Highlighted
Helper II

## Re: Cumulative total with multiple item and time

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!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021