DAX measure to find monthly price change.

[AShiraz2023]

Hello All,

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: Result i am looking for.

thank you for your help in advance.

[AShiraz2023]

Hello @some_bih, above sloution works for MOM change, thank you. however when i add previous month column to my pivot table it doesn't show any number. can you let me know how can i fix this issue. in addition to that since my data is coming from part receiving table smetime i don't receive part in 2 consecutive month like part recived in Jan than March or April insted of Feb. is there anyway i can filter parts if its not recienved in particular month and show only items price varience where part recived because with your current sloution if i don't recived part than it shows blank value in sum of price and rest of the measure. thank you in advance for your help.

[some_bih]

Hi @AShiraz2023 I did as it was shown on example.

For your new scenario:

- adding new columns - please not that MOM measure should work when there is some columns from Date / Calendar table. So use columns for year / month from your Calendar / date table.

- I do not know details how you prepare your data. Measure should work as we use columns for year / month from your Calendar / date table where there are consecutive dates. In this scenario you will have just lag of months for data, but caclculation should work.

Did I answer correctly? Kudos appreciate / accept solution.

[AShiraz2023]

I import my part receving data from folder using power query. it shows all spend columns such as part, plant, category, subcategory vandor, quantity, price, and part reciving date, than i load to data model in excel and create date table as you mention before. now i  run analysis on mom price change as per your sloution. next i need to add some dax measure to analysis monthly and annual spend by category and vendor or subcategory ect. once this is done than based on quantity column i like to create forecast for each part lets say for next 5 year based on current usage. is this possible?

[AShiraz2023]

Hello @some_bih , Tank you for solving the issue. is there anyway i can workaround and genrate forecast in excel data model since i am not using power bi? 

[some_bih]

Hi @AShiraz2023 in Excel, sure. Example is on the link below. On web there are many resources for forecastin in Excel.

FORECAST Function in Excel (Example, Formula) | How to use? (wallstreetmojo.com)

[some_bih · Accepted solution]

Hi @AShiraz2023 for forecasting in Power BI please check link

Use the Analytics pane in Power BI Desktop - Power BI | Microsoft Learn

If solution above is working please accept solution so other member of community could use it. Thank you.

[some_bih]

Hi @AShiraz2023 for you case please create Calendar / Date table, if you already do not have one in the model and create relationship, then create measures as below. Output should be as your.

Did I answer correctly? Kudos appreciate / accept solution.

Sum of Price = SUM(Sheet1[Price])

PM Sum of Price =

 //previous month price

    CALCULATE (

        [Sum of Price],

        CALCULATETABLE (

            DATEADD ( 'Date'[Date], -1, MONTH )

        )

    )

MOM Change in price =

//calculation of change in price month over month

VAR __current_month = [Sum of Price]

VAR __previous_month = [PM Sum of Price]

VAR __Result =

    IF (

        NOT ISBLANK ( __current_month ) && NOT ISBLANK ( __previous_month ),

        __current_month - __previous_month,

        0

)

RETURN

__Result

 Output