In Part 1 of this series we will cover the basic measures we will require for the rest of the series to enable or intelligent/dynamic filtering for the relevant fiscal periods.
The fiscal period referenced in this series as well as previous posts is from July to June. We will highlight what you will need to change if your fiscal period is different. While we are based in Australia and the most common fiscal period is the Tax year, there are differences between organisations and the expressions are easily adjusted.
Shall we begin...
- You have an existing imported query (live connections are not supported when creating additional tables)
- You are familiar with Power BI Desktop interface
- You do not need the Holiday Calendars for this series, although it is useful for reporting if you need it.
- If everyday is a working day for your business, this series does not cater for it, but you can make the changes to the queries as required.
Setting up for the comparisons...
To perform the Year-on-Year comparisons we need setup some base measures that will govern the dynamic generation of Date Range tables we will build in part 2.
NOTE: We are only working with our Calendar table in this post, we have hidden all these measures from the Report view and you can choose to do so.
- First, we need to know what the current date is, easy, create the following measure
.Today = DATE(YEAR(NOW()), MONTH(NOW()), DAY(NOW()))
- And then the same time last year
.Today LY = DATE(YEAR(NOW())-1, MONTH(NOW()), DAY(NOW()))
These setup the foundation of the rest of the work to be done, as without these the dynamic tables... well... just aren't "dynamic" :)
Now for the period start dates:
- Start of the Month, current and Last Year:
.StartOfMonth = DATE(YEAR([.Today]),MONTH([.Today]),1)
.StartOfMonth LY = DATE(YEAR([.Today LY]),MONTH([.Today LY]),1)
- Start of the Quarter current and Last Year:
.StartOfQuarter = DATE(YEAR([.Today]), SWITCH(INT((MONTH([.Today])+2)/3), 1, 1, 2, 4, 3, 7, 4, 10, 0), 1)
.StartOfQuarter LY = DATE(YEAR([.Today LY]), SWITCH(INT((MONTH([.Today LY])+2)/3), 1, 1, 2, 4, 3, 7, 4, 10, 0), 1)
The SWITCH is rather ugly, but basically it gets the current quarter and then based on that sets the first month of the quarter accordingly.
You will need to tweak this if your fiscal quarters do not follow the typical quarters. i.e.: if you quarter starts in November instead of October.
(If there is a better solution, please send your comments.)
- Start of the Year, current and Last Year:
.StartOfYear = DATE(IF(MONTH([.Today])<=6,YEAR([.Today])-1,YEAR([.Today])),7,1)
.StartOfYear LY = DATE(IF(MONTH([.Today LY])<=6,YEAR([.Today LY])-1,YEAR([.Today LY])),7,1)
You can perform a sanity check of the dates by adding the measures to Multi-Row Card:
As you can see we built this this month and thus all the dates are pretty much the same as our Month, Quarter and Fiscal Year all start at the same point of the year.
Remember this is still going to show a correct calendar date, even though it is FY2018, at the time of writing, it is not going to change the year, that will just mess with everything.
Its just our filtering for the "Year" is based on the FISCAL YEAR column you would have created in the calendar prior to this process which would allow you to select "FY2018" while the date range is from 1 July 2017 to 30 June 2018.
In financial terms, it is FYE or Financial/Fiscal Year End 2018, meaning the financial or fiscal year ends in 2018.
Bonus... Simple Measures that are Useful:
- Current (Fiscal) Month
"Fiscal" is relative, it is still the same month, but at least in reporting is looks better.
CurrentFiscalMonth = LOOKUPVALUE('Calendar'[FiscalMonth], 'Calendar'[Date].[Date], [.Today])
- Current Fiscal Quarter
CurrentFiscalQuarter = LOOKUPVALUE('Calendar'[FiscalQuarter], 'Calendar'[Date].[Date], [.Today])
- Current Fiscal Year
CurrentFiscalYear = LOOKUPVALUE('Calendar'[FiscalYear], 'Calendar'[Date].[Date], [.Today])
A final sanity check should produce the following:
This concludes Part 1.
In Part 2, we generate the Dynamic date range tables all based on these measures we have just created and thus eliminating the need to have complex IF and DATE range expressions in our measures in our data sets knowing that that measure will always be current and valid.
See you then.