Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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 |
Solved! Go to 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]) ))
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 |
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 |
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.
@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]) ))
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |