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 examp...
  • 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
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
Icon for Solution Sage rankSolution 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