In Part 1 we covered the setup for the Fiscal Comparison and is pre-requisite to this post.
In Part 3, the final part in this series, we create the measures that will use what we have created.
Part 2 focuses on the creation of the Date Range tables for our measures.
Some Background info...
Selecting data based on date ranges can result in some very complex DAX expressions that while do work, can also cause some headaches in the long run. This is where we came up with the idea of dynamics tables that update on refresh based on the simple measures we have already defined that to retrieve the relevant dates without us having to worry about it. Set and Forget. A bonus for anyone having to deal with those archaic spreadsheets and macros that require constant copy/paste.
These tables are in effect, rolling Current Month, Current Quarter and Current Year (Fiscal or Calendar - based on what you have configured) tables that will, upon refresh, update and any measure using them will update and report accordingly.
This is great solution especially once you publish to the Power BI service and enable the regular refresh through your personal or enterprise gateway, users will then see the latest information for the up-to-date period without worry that expressions on your measures need to be updated.
Agreed, you could merge these expressions into each of the relevant measures, but, that would mean you would be duplicating that long expression into every measure that needs the filter... which means if we need to make a change, like the fiscal period of a company changes because of... say... a takeover or merger and the new parent company has a different fiscal period (poor example, but you get the point) you will have to have update EVERY measure, so why not limit the locations that require this change?
Beyond that, we can get started...
How to... Configure the Dynamic Date Range Tables...
Summary of points we will cover:
- Generation of "To Date" tables for the Month, Quarter and Year of the Current and Last Year periods
- Generation of "Full" tables for the Month, Quarter and Year of the Current and Last Year periods
When adding any of the below, use "Modeling" then "New Table"
- "Month To Date" current year:
DATES_MTD = DATESBETWEEN('Calendar'[Date], [.StartOfMonth], [.Today])
- "Month To Date" last year:
DATES_MTD_LY = DATESBETWEEN('Calendar'[Date], [.StartOfMonth LY], [.Today LY])
- "Full Month" current year:
DATES_MTH = DATESINPERIOD('Calendar'[Date], [.StartOfMonth], 1, MONTH)
- "Full Month" last year:
DATES_MTH_LY = DATESINPERIOD('Calendar'[Date], [.StartOfMonth LY], 1, MONTH)
- "Quarter To Date" current year:
DATES_QTD = DATESBETWEEN('Calendar'[Date], [.StartOfQuarter], [.Today])
- "Quarter To Date" last year:
DATES_QTD_LY = DATESBETWEEN('Calendar'[Date], [.StartOfQuarter LY], [.Today LY])
- "Full Quarter" current year:
DATES_QTR = DATESINPERIOD('Calendar'[Date], [.StartOfQuarter], 3, MONTH)
- "Full Quarter" last year:
DATES_QTR_LY = DATESINPERIOD('Calendar'[Date], [.StartOfQuarter LY], 3, MONTH)
- "Year To Date" current year:
DATES_YTD = DATESBETWEEN('Calendar'[Date], [.StartOfYear], [.Today])
- "Year To Date" last year:
DATES_YTD_LY = DATESBETWEEN('Calendar'[Date], [.StartOfYear LY], [.Today LY])
- "Full Year" current year:
DATES_YR = DATESINPERIOD('Calendar'[Date], [.StartOfYear], 1, YEAR)
- "Full Year" last year:
DATES_YR_LY = DATESINPERIOD('Calendar'[Date], [.StartOfYear LY], 1, YEAR)
So... What happens?
- Each of the "...To Date" expressions look at the "Date" column in our Calendar table, and using the relevant "Start of..." and ".Today" measures generate the range of dates for that period.
- Each of the "Full..." expressions again look at the "Date" column in our Calendar table, and beginning with the relevant "Start of..." measure and stipulating a MONTH or YEAR value to generate the full period in which the date range should be generated.
Using the "Start of..." measures we created, ensures that no matter what MONTH, QUARTER or YEAR we are actually in, those ranges will never be incorrect. Due to the fact that when you move over into a new month, quarter or year, the "Start of..." measures are updated on the refresh and hey presto your ranges are updated.
And of course with your Calendar dates based on your imported query, everything ties in together nicely
Note: Remember to hide these tables from the Report View as you wont need their data in the report view, but you will still be able to reference them in your measures you create.
And finally... the relationships...
- Connect the "Date" column from each of your new date range tables to the "Date" column in your calendar.
- There is one issue with the relationships though, as "One to one (1:1)" relationships they skew the measures we create later on, and here comes the manual labour part, you need to update each relationship manually to a "Many to one (*:1). You can leave everything else as the same.
- This occurs due to there been only one column and a single, unique value for each date in the tables and as such Power BI Desktop automatically sets the relationship to one we don't want.
- Ultimately once complete, you should have something similar to the following:
Here we come to the conclusion of Part 2 of this series.
Next stop, the Measures, see you there.