Cumulative total with multiple item and time

[bryanrendra]

 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

[amitchandak · Accepted solution]

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

[Anonymous · Accepted solution]

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

[bryanrendra]

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.

[amitchandak · Accepted solution]

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

[bryanrendra]

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!