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

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