cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
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]))
 
ItemOriginal PriceDateIncremental Price Updated Price logic
Pencil $                       3.001/1/2019  $                       3.00>Ori + Incr1
Pencil $                       3.002/1/20190.8 $                       3.80>Ori + Incr1 +Incr2
Pencil $                       3.003/1/2019  $                       3.80>Ori + Incr1+Incr2+Incr3
Pencil $                       3.004/1/20190.2 $                       4.00So on..
Pencil $                       3.005/1/20190.3 $                       4.30 
Pencil $                       3.006/1/2019  $                       4.30 
Book $                       5.001/1/20191 $                       6.005+1
Book $                       5.002/1/2019  $                       6.00 
Book $                       5.003/1/2019  $                       6.00 
Book $                       5.004/1/20193 $                       9.005+3+1
Book $                       5.005/1/2019  $                       9.00 
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IX
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]) ))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Solution Sage
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

 

ItemOriginal PriceDateIncremental Price
Pencil301-Jan-19 
Pencil302-Jan-190.8
Pencil303-Jan-19 
Pencil304-Jan-190.2
Pencil305-Jan-190.3
Pencil306-Jan-19 
Book501-Jan-191
Book502-Jan-19 
Book503-Jan-19 
Book504-Jan-193
Book505-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.

 

ItemOriginal PriceDateIncremental PriceUpdated Price
Pencil301-Jan-19 3
Pencil302-Jan-190.83.8
Pencil303-Jan-19 3.8
Pencil304-Jan-190.24
Pencil305-Jan-190.34.3
Pencil306-Jan-19 4.3
Book501-Jan-1916
Book502-Jan-19 6
Book503-Jan-19 6
Book504-Jan-1939
Book505-Jan-19 9

 

View solution in original post

4 REPLIES 4
Highlighted
Super User IX
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]) ))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Solution Sage
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

 

ItemOriginal PriceDateIncremental Price
Pencil301-Jan-19 
Pencil302-Jan-190.8
Pencil303-Jan-19 
Pencil304-Jan-190.2
Pencil305-Jan-190.3
Pencil306-Jan-19 
Book501-Jan-191
Book502-Jan-19 
Book503-Jan-19 
Book504-Jan-193
Book505-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.

 

ItemOriginal PriceDateIncremental PriceUpdated Price
Pencil301-Jan-19 3
Pencil302-Jan-190.83.8
Pencil303-Jan-19 3.8
Pencil304-Jan-190.24
Pencil305-Jan-190.34.3
Pencil306-Jan-19 4.3
Book501-Jan-1916
Book502-Jan-19 6
Book503-Jan-19 6
Book504-Jan-1939
Book505-Jan-19 9

 

View solution in original post

Highlighted
Helper II
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
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!

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

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

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors
Users online (769)