Help! Month over Year calculation/comparison

[romelend]

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:

• Total Contract Value
• Contract Start Date
• Contract End Date

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 always compare the same month to the previous year). How can I do that?

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.

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!

Thanks in advance for the help!

[romelend · Accepted solution]

I found this response on the Microsoft Community that will ultimately get me where I need to go: https://community.fabric.microsoft.com/t5/Desktop/spread-revenue-over-future-months/td-p/607050

The response from v-jiascu-msft was the answer I needed!

[romelend]

Figured I'd give a generic example that may help:

Customer X's initial contract was January 1, 2022 through December 31, 2022. Their TCV was $12,000, so their MRR is $1,000 in 2022. When they renewed their contract in 2023, we doubled their contract (super unlikely but makes for easy math). So, for January 1, 2023 through December 31, 2023, their TCV is now $24,000 so their MRR is $2,000 in 2023.

If I want to look at Customer X's growth and compare specifically October 2023 against October 2022, I should be able to tell we upsold them and the difference is $1,000 MRR.