CODEWITHSUNDEEP
exceldaxdata-modeling
Ahmer
Ahmer

Reputation: 1

DAX Measure to calculate Monthly price change

enter image description here

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 diffrence need to calculated

price change result with DAX measure (current result with above dax measure)

required result cloumn(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 occuer. if price is not changed from previous month to current month than result should be 0, else show the diffrence of price change. I am using excel data nodel to achive this task. data come from transactional table.

Upvotes: 0

Views: 113

Answers (1)

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

Try the following :

Monthly Price Change=
VAR CurrentDate = MAX(PriceChange[End of Month])
VAR CurrentPart = MAX(PriceChange[Partlist])
VAR CurrentMonthPrice = 
    CALCULATE(
        MAX(PriceChange[PO Price]),
        PriceChange[End of Month] = CurrentDate,
        PriceChange[Partlist] = CurrentPart
    )
VAR PreviousMonthPrice = 
    CALCULATE(
        MAX(PriceChange[PO Price]),
        PriceChange[End of Month] = EOMONTH(CurrentDate, -1),
        PriceChange[Partlist] = CurrentPart
    )
RETURN
IF(ISBLANK(CurrentMonthPrice) || ISBLANK(PreviousMonthPrice), 
    BLANK(),
    CurrentMonthPrice - PreviousMonthPrice
)

Upvotes: 0

Related Questions