CODEWITHSUNDEEP
powerbipowerbi-desktop
rmel
rmel

Reputation: 1

Month over Year calculation/comparison

I have a situation where I need to compare a month in one year to a month in a previous year. For example, I want to be able to see the MRR (monthly recurring revenue) in November 2023 and how it compares to the MRR for November 2022.

I have:

Currently, we are creating separate columns in excel for every month of the year and doing a relatively complicated formula to capture the MRR for each month and then we create a pivot table with that information and then two more tables that show the comparison between months of different years. So, if we're doing YOY comparisons for January 2019 through December 2023, I'll have 60 columns in excel. Needless to say, this isn't very scalable and is incredibly manual so I'm hoping Power BI will have a better solution!

Bottom Line: If a contract is January 2023 through December 2023, I want to see how the MRR compares from July 2023 to July 2022 (we will always compare to the same month of the previous year). How can I do that?

Unfortunately, I cannot provide any data samples as this data is confidential but I am happy to go into more detail from a high level if needed!

I've started with placing my raw data in PBI and creating a calendar table that autogenerates and provides a monthly view (so one row for every month of the year from January 2019-December 2023). But, I'm stuck at this point on how to move forward.

Upvotes: 0

Views: 430

Answers (1)

Pieter
Pieter

Reputation: 724

You can use CALCULATE() and then shift the evaluation context of the measure back a year...

PY MRR =   CALCULATE( [MRR], SAMEPERIODLASTYEAR(DimDate) )

and PowerBI will take care of the date shifting. Then you can calculate the delta by doing something like this:

[MRR] - [PY MRR]

delta % = DIVIDE ([MRR] - [PY MRR], [PY MRR])

Upvotes: 0

Related Questions