I am working on transactional data and product price change monthly or sometime every 2nd or 3rd month it can go up or down. I try to write dax measure in Excel data model, but the result is not what i need. Can someone help? Here is the data I am working with.
DimkeyPartlistIndexEnd of MonthPriceCurrencyPrice Changerequired result
Dimkey Partlist Index End of Month Price Currency Price Change required result
33299 Product0 0 2018-01-31 1.0839 USD 0.0016 0
33301 Product0 0 2018-02-28 1.0839 USD 0.0016 0
33297 Product0 0 2018-03-31 1.0839 USD 0.0016 0
33294 Product0 0 2018-04-30 1.0839 USD 0.0016 0
33295 Product0 0 2018-05-31 1.0839 USD 0.0016 0
33293 Product0 0 2018-06-30 1.0839 USD 0.0016 0
33296 Product0 0 2018-07-31 1.0839 USD 0.0016 0
33292 Product0 0 2018-08-31 1.0855 USD 0.0016 0.0016
33302 Product0 0 2018-09-30 1.0855 USD 0.0016 0
33300 Product0 0 2018-10-31 1.0855 USD 0.0016 0
33303 Product0 0 2018-11-30 1.0855 USD 0.0016 0
33298 Product0 0 2018-12-31 1.0855 USD 0.0016 0
31746 Product1 1 2018-01-31 7.96 USD 0.36 0
31745 Product1 1 2018-02-28 7.96 USD 0.36 0
31748 Product1 1 2018-03-31 7.96 USD 0.36 0
31752 Product1 1 2018-04-30 8.06 USD 0.36 0.1
31751 Product1 1 2018-05-31 8.06 USD 0.36 0
31754 Product1 1 2018-06-30 8.32 USD 0.36 0.26
31747 Product1 1 2018-07-31 8.32 USD 0.36 0
31744 Product1 1 2018-08-31 8.32 USD 0.36 0
31753 Product1 1 2018-09-30 8.32 USD 0.36 0
31743 Product1 1 2018-10-31 8.24 USD 0.36 -0.08
31750 Product1 1 2018-11-30 8.24 USD 0.36 0
31749 Product1 1 2018-12-31 8.09 USD 0.36 -0.15
Current DAX measure:
Monthly Price Change:=VAR MaxDate = MAX(PriceChange[End of Month])
VAR MinDate = MIN(PriceChange[End of Month])
VAR MaxPrice = CALCULATE(MAX(PriceChange[PO Price]), ALLEXCEPT(PriceChange, PriceChange[Partlist]))
VAR MinPrice = CALCULATE(MIN(PriceChange[PO Price]), ALLEXCEPT(PriceChange, PriceChange[Partlist]))
RETURN
IF(ISBLANK(MaxPrice) || ISBLANK(MinPrice), BLANK(), MaxPrice - MinPrice)
where price change occur: based on date column price difference need to calculated
price change result with DAX measure (current result with above dax measure)
required result column(hard coded at this time) : Result i am looking for with Dax measure.
thank you for your help in advance.
I am trying to calculate Price change on monthly bases from previous month on whenever it occur. if price is not changed from previous month to current month than result should be 0, else show the difference of price change. I am using excel data model to achieve this task. Data come from transactional table.