BBampaa

Lessons › Power BI

Power BI lesson

Last month, every month

Read the bars as last month's revenue: February's bar is January's 570, March's bar is February's 598. January has no bar, because there's no month before it.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

In level 12 you typed one measure per month. With three years of data, that would be 36 measures. This one measure gives every month the revenue of the month before:

Last Month Revenue = CALCULATE([Total Revenue], DATEADD(Dates[Date], -1, MONTH))

Key points

A date tableA table with one row for each date. It tells Power BI which month comes before which. Here it's called Dates.
DATEADDMoves the dates. DATEADD(Dates[Date], -1, MONTH) has three parts: the date column, how far (-1 means one back), and the step size (MONTH).
Inside CALCULATECALCULATE([Total Revenue], DATEADD(...)) works out Total Revenue for the moved dates: last month.

The same shape every time: CALCULATE([Measure], DATEADD(Dates[Date], -1, MONTH)). Swap the measure to get last month's orders, items or anything else.

In Power BI Desktop

First tell Power BI which table holds your dates: select the Dates table, then Table tools, then Mark as date table. Then Home, then New measure, and type the formula. DATEADD only works with a date table.

Practice

Try each one in your head first, then open the answer.

1. Build the formula for Last Month Revenue.
Last Month Revenue = CALCULATE([Total Revenue], DATEADD(Dates[Date], -1, MONTH))
2. Write a measure for last month's orders.
Last Month Orders = CALCULATE([Orders], DATEADD(Dates[Date], -1, MONTH))
3. Write a measure for last month's items sold.
Last Month Qty = CALCULATE([Total Qty], DATEADD(Dates[Date], -1, MONTH))
4. Why is January empty in Last Month Revenue?
The data has no month before January
5. Write a measure for growth on last month: this month's revenue minus last month's, divided by last month's.
Growth on Last Month = DIVIDE([Total Revenue] - [Last Month Revenue], [Last Month Revenue])

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed