Month over Month % Variance

[filipebodas]

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:

Year	Total Orders
2021	10.000
November	5.500
December	4.500
2022	7.000
January	7.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.

Year	Total Orders	Last Month Orders	Second Last Month Orders
2021	10.000
November	5.500	7.000	4.500
December	4.500	7.000	4.500
2022	7.000
January	7.000	7.000	4.500

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

[filipebodas · Accepted solution]

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

[Whitewater100]

Hi: This has some additional control tools in your date table. Does this work for you?

https://drive.google.com/file/d/1Wrt1MD9hqoezFCfZYCftUHDK0FRO8nDG/view?usp=sharing 

[filipebodas]

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

[Whitewater100]

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