Forum Discussion

preethisaldanha's avatar
preethisaldanha
Frequent Visitor
2 years ago
Solved

Finding difference between price in current and previous month

Hi All,

I am trying to find the price change for products between this month and last month but it is not giving me the correct result for previous month and price change using the below. Please can someone help me.

 

Previous Month =

Var PM = CALCULATE([Current Month],PREVIOUSMONTH('Price Index'[Month]))
RETURN
if(ISBLANK(PM) || PM = 0 , 0,PM)
 
Current Month = calculate(sum(Price Index'[Competitor Price]),'Price Index'[Month] =  MAX('Price Index'[Month]))
 
Price change = Calculate(IF([Previous Month]=0, 0,[Current Month]-[Previous Month]),'Price Index'[Month] =  MAX('Price Index'[Month]))
  • DataNinja777's avatar
    DataNinja777
    2 years ago

    Hi preethisaldanha 

     

    Instead of using MAX('Price Index'[Month]), which is a date field (or just a month number) in your fact table, I recommend creating a separate calendar dimension table. Establish a relationship between the date field in your fact table and the calendar table to enable more flexible analysis. With your current approach, if MAX('Price Index'[Month]) only represents the month number, January 2024 could be considered less than December 2023, which is incorrect.

     

    Accordingly, I've modified the dax formula as shown below:

    Previous Month = 
VAR PM = 
    CALCULATE(
        [Current Month],
        PREVIOUSMONTH('Calendar'[Date])
    )
RETURN
    IF(ISBLANK(PM) || PM = 0, 0, PM)

     

    Current Month = 
CALCULATE(
    SUM('Price Index'[Competitor Price]),
    'Calendar'[Date] = MAX('Calendar'[Date])
)

     

    Price Change = 
CALCULATE(
    IF([Previous Month] = 0, 0, [Current Month] - [Previous Month]),
    'Calendar'[Date] = MAX('Calendar'[Date])
)

     

    Best regards,

     

2 Replies

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    2 years ago

    Hi preethisaldanha 

     

    Instead of using MAX('Price Index'[Month]), which is a date field (or just a month number) in your fact table, I recommend creating a separate calendar dimension table. Establish a relationship between the date field in your fact table and the calendar table to enable more flexible analysis. With your current approach, if MAX('Price Index'[Month]) only represents the month number, January 2024 could be considered less than December 2023, which is incorrect.

     

    Accordingly, I've modified the dax formula as shown below:

    Previous Month = 
VAR PM = 
    CALCULATE(
        [Current Month],
        PREVIOUSMONTH('Calendar'[Date])
    )
RETURN
    IF(ISBLANK(PM) || PM = 0, 0, PM)

     

    Current Month = 
CALCULATE(
    SUM('Price Index'[Competitor Price]),
    'Calendar'[Date] = MAX('Calendar'[Date])
)

     

    Price Change = 
CALCULATE(
    IF([Previous Month] = 0, 0, [Current Month] - [Previous Month]),
    'Calendar'[Date] = MAX('Calendar'[Date])
)

     

    Best regards,