From my experience developing reporting solutions in Power BI, one of the most common requests, regardless of industry, is tracking changes over time. Whether it’s comparing this month’s costs to last month’s, monitoring budget fluctuations, or spotting delays in project timelines, understanding trends is critical.
Power BI makes it easy to analyse data across different time periods, but there’s a catch: if you don’t set things up properly, your calculations might not work as expected.
One of the biggest mistakes people make? Skipping the Date Dimension Table.
If you haven’t created one yet, check out my guide on setting up a Date Dimension Table in Power BI, trust me, it’s a game-changer!
Understanding Month-over-Month (MoM) Change with Time Intelligence Functions
Month-over-Month change is a simple but powerful way to track how a metric, such as costs and revenue, fluctuates over time. It helps identify trends, seasonal patterns, and anomalies, making it an essential calculation for data-driven decision-making.
At its core, the MoM calculation consists of two key components:
- Absolute Change: How much the value has increased or decreased compared to the previous month.
- MoM Absolute Change=Current Month Value−Previous Month Value
- Percentage Change: The relative difference as a percentage of the previous month’s value.
- MoM Percentage Change= (ValueMoM Absolute Change/ Previous Month) * 100
Now, in Power BI, Time Intelligence functions make it easy to retrieve the previous month’s value dynamically, no need to manually filter or shift your dataset! These functions allow us to work with date-based calculations effortlessly. Some of the most commonly used ones include:
- PREVIOUSMONTH() – Retrieves data from the previous month based on your date column.
- SAMEPERIODLASTYEAR() – Compares values from the same period in the previous year.
- TOTALYTD(), TOTALQTD(), TOTALMTD() – Calculate year-to-date, quarter-to-date, or month-to-date totals.
Using the function PREVIOUSMONTH(), we can efficiently calculate MoM changes and visualise trends in a structured, automated way. Now, let’s walk through an example and apply this concept in Power BI!
MoM Change: A Practical Example
On this example we’ll calculate:
- Total Costs
- Costs for the Previous Month (PM)
- Absolute MoM Change (how much costs increased or decreased)
- Percentage MoM Change (the percentage difference compared to the previous month)
Then we will create a Matrix to illustrate the changes.
Step 1: Create a Sample Dataset
To follow along, here’s a simple dataset you can load into Power BI (Facts_Costs):
Step 2: Create a Date Dimension table
For this example, use this DAX. Under the Table View > New Table > copy the DAX
Dim_Date =
ADDCOLUMNS(
CALENDAR(DATE(2024,1,1), DATE(2025,4,30)),
"Year", YEAR([Date]),
"Quarter", "Q" & FORMAT([Date], "Q"),
"Financial Year", IF(MONTH([Date]) >= 4, YEAR([Date]) & "/" & YEAR([Date]) + 1, YEAR([Date]) - 1 & "/" & YEAR([Date])),
"End of Month", FORMAT(EOMONTH([Date], 0), "dd/MM/yyyy")
)
Mark the Dim_Date as Date Table (using the column Date).
Step 3: Create the Data Model
On the Data Model view, create a relationship between the Facts_Costs[Pivot Period] and Dim_Date[Date].
Step 4: Create the Measures
For the Total Cost (current month), use the DAX:
Total Costs = SUM('Facts_Costs'[Actual Spent])For the Total Costs from the previous month, use the DAX:
Total Costs PM =
CALCULATE(
SUM(Facts_Costs[Actual Spent]),
PREVIOUSMONTH('Dim_Date'[Date])
)For the absolute month over month movement, use the DAX:
MoM_Absolute_Change =
IF(
ISBLANK([Total Costs PM]),
BLANK(),
[Total Costs] - [Total Costs PM]
)By adding the conditional statement in our DAX code, we handle situations where [Total Costs PM] (Total Costs Previous Month) is blank. This usually happens for the first entry in your table because there’s no previous month to reference. Returning blank in these cases ensures our calculations remain meaningful and avoids any misleading results.
For the percentual month over month movement, use the DAX:
MoM_Percentage_Change =
DIVIDE([MoM_Absolute_Change], [Total Costs PM], 0)Format this measure as a percentage to eliminate the need for multiplying the total by 100.
Step 5: Create the visualisation
From the visualisations pane, add a MATRIX:
Rows: Add Project Code
Values: Add the measures:
- Total Costs
- Total Costs PM
- MoM_Percentage_Change
To ensure accurate calculations and meaningful visualisation in this step, it’s important to add context for the current month. This can be done by using a filter or a slicer. Without this context, the measure for Previous Month (Total Costs PM) won’t calculate correctly, as it relies on knowing which specific month’s data it should reference.
Step 6: Format your visual
With accessibility in mind, ensure your visual:
- Has a meaningful title
- The font size is above 12 pts for documents or above 14 pts for presentations
- Watch the colour contrast! Make sure the colour contrast ration between text and background is of at least 4.5:1. If you are not sure how to calculate the colour contrast ratio, watch this tutorial where I walk through each step Calculating Colour Contrast Ratio.
- Add alt text, with no more than 250 characters
- Adding conditional formatting: make sure that colour is not the only way to convey information. For example, instead of formatting the font colour, consider adding an icon.
See the example bellow:
Where the following conditional formatting was used for the MoM %:
The following Alt Text was used:
“A table titled “Cost Per Project: Current Month vs Previous Month” shows monthly costs for all projects for the current and previous months. Columns include Project Code, Current Month, Previous Month, and Month over Month %. For a screen-friendly view, use “Show as a Table” (Alt+Shift+F11).”
Wrapping up
If you’ve ever struggled with month-over-month calculations or found Power BI’s time functions confusing, I hope this guide has helped! Give these techniques a try, and let me know how they work for you. And if you’re a visual learner, stay tuned, I’ll be sharing a step-by-step video demo on my YouTube channel soon, so make sure you subscribe to Learning Power BI & Accessible Design – YouTube.
Thank you for joining me on this journey. Until next time, let’s keep crafting accessible insights that make a difference!
