Posted by To calculate a prior period formula in Power BI, you don’t need a Date table. Lots of consultants will suggest to build one as a best practice, but if you want to save yourself some model and visual rework, there’s other options.
In the example formulas that follow, I imported a sales.csv file I got from Microsoft’s Dashboard in a Day training which looks like the featured image of this post. This is provided so you can follow along with my syntax.
The Date Table Method
Creating a Date table is not hard. A basic one could be stood up in Power BI Desktop by going to the Modeling tab, hitting the New Table button and writing the formula: Date = CALENDAR ( "1/1/2011", "1/1/2019" ).
Create a relationship from your Sales[Date] column to your Date[Date] and add a measure that reads Prior Month = CALCULATE ( SUM ( 'Sales'[Revenue] ), PREVOUSMONTH ( 'Date'[Date] ) ).
However, what this textbook approach doesn’t consider is the amount of rework on the visuals you may have already dialed in on your report pages and dashboard tiles. Meaning, if you’ve used Sales[Date] all over your trends and slicers and tables, will this change mess you up? Yes it will. Don’t believe me? Try it. For example, you’ll see column charts turn into a heart scan.
So what other options are there? Let’s take a look.
Use the built-in Date table
Our Sales[Date] column has a Date table built-in, you just can’t see it. You can rewrite the formula above using this built-in date table instead:
Check out an article by SQLBI to learn more about using automatic time intelligence and the Auto Date/Time setting in your report it depends on.
No Date Table Method
Here’s a formula that does the same thing as the above:
| Revenue PM = | |
| var prior_month_start_date = EOMONTH( MIN ( Sales[Date] ), -2 ) + 1 | |
| var prior_month_end_date = EOMONTH( MIN ( Sales[Date] ), -1 ) + 1 | |
| RETURN | |
| CALCULATE( | |
| SUM ( Sales[Revenue] ), | |
| FILTER ( | |
| ALLSELECTED ( Sales ), | |
| Sales[Date] >= prior_month_start_date && Sales[Date] < prior_month_end_date | |
| ) | |
| ) |
It ain’t pretty, but necessary since our Sales table does not have a continous date column.
The two first lines are variable statements for the beginning and ending of the prior month date range. You can change the names if you have better names that make sense to you.
Then in the CALCULATE statement, we do the same SUM as before, but instead of PREVIOUSMONTH which requires a Date table, we FILTER the Sales table using the ALLSELECTED function to ignore the filter context of our trend, respect all other filters on our page, and then narrow it down where the [Date] column is within that range.
Summary
These alternate formulas above may work for you, they may not. For instance, using the built-in table won’t work for weeks; only years, quarters, and months. The “No Date Table” method won’t work if you add attributes besides Month in your visual. Bottom line, there is good reason why adding a Date table to your model is a best practice.
Let me know in the comments if this doesn’t make sense and I’ll revise the article. I mainly just want to debunk the myth that you need a separate Date table in all cases. You don’t. Yes, its a best practice if you have the time to implement, but when you don’t, there’s a plan B and C.
Thanks for reading!
Update 11/22/2019: Thanks to my buddy Nathan Peterson for pointing out that FILTERing the Sales table using the ALL function removes all other filters applied, which is not a desired outcome.
Update 11/24/2019: After stumbling on SQLBI’s article on automatic time intelligence, I tried the Month-over-month Quick Measure in Power BI and learned that you can access the internal Date tables in Power BI. You just have to use the right syntax.
Angel Abundez's Blog 