Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Month over Month % Variance

Hi everyone,

 

I want to calculate a month over month variation, but I want to lock the current value of the last and second last month, even if my date slicer has two or more months.

 

For example, if my matrix table has:

 

YearTotal Orders
  •   2021
10.000
             November 5.500
             December4.500
  •   2022
7.000
             January7.000

 

I want to lock the values of the latest month and second latest month of the interval everytime I move the slicer on my date so I can calculate the variation over month over month.

 

YearTotal OrdersLast Month Orders Second Last Month Orders
  •   2021
10.000  
             November 5.5007.000               4.500
             December4.5007.000               4.500
  •   2022
7.000  
            January7.0007.0004.500

 

Then I can calculate the variance betwen the latest and second latest month of my interval

  • Anonymous's avatar
    Anonymous
    4 years ago

    I solved. This is what I did:

    Orders Month Over Month =

    var most_recent_month_orders =
    CALCULATE (
    'Measure Table'[Total Orders],
    'Calendar'[Month] = MONTH (MAX ('Calendar'[Date])),
    'Calendar'[Year] = YEAR (MAX ('Calendar'[Date])))

    var second_most_recent_month_orders =
    CALCULATE (
    'Measure Table'[Total Orders],
    'Calendar'[Month] = MONTH (MAX ('Calendar'[End of Previous Month])),
    'Calendar'[Year] = YEAR (MAX ('Calendar'[End of Previous Month])))

    return
    DIVIDE (
    most_recent_month_orders,
    second_most_recent_month_orders,
    0) - 1



    Whereas, Calendar'[End of Previous Month] is a column in my Calendar table I calculated by subtrating 1 day to the [Start of Month] column

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      It appears to do so, but your MoM metrics always returns blank regardless of the interval date

      • Whitewater100's avatar
        Whitewater100
        Solution Sage
        4 years ago

        Hi:

        Can you take another look? I manually entered only three months of data. If more months had data it will work for more months. Currenly Nov '21 thru Jan '22 are populated.  See image below. Slicer is working.

        Are you saying you always want to see the last 30 days compared to days 60-31 ago?

        What result do you expect?

        Thanks

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I solved. This is what I did:

    Orders Month Over Month =

    var most_recent_month_orders =
    CALCULATE (
    'Measure Table'[Total Orders],
    'Calendar'[Month] = MONTH (MAX ('Calendar'[Date])),
    'Calendar'[Year] = YEAR (MAX ('Calendar'[Date])))

    var second_most_recent_month_orders =
    CALCULATE (
    'Measure Table'[Total Orders],
    'Calendar'[Month] = MONTH (MAX ('Calendar'[End of Previous Month])),
    'Calendar'[Year] = YEAR (MAX ('Calendar'[End of Previous Month])))

    return
    DIVIDE (
    most_recent_month_orders,
    second_most_recent_month_orders,
    0) - 1



    Whereas, Calendar'[End of Previous Month] is a column in my Calendar table I calculated by subtrating 1 day to the [Start of Month] column