What is a special fiscal period?
Some organisations use special fiscal periods like 5-4-4 to maintain comparability between years. In some instances, they are referred to as Factory calendars, Retail calendars, or Special Fiscal Variants. A year is divided into 4 Quarters, and each quarter is further divided into three months with five weeks, four weeks and four weeks, respectively.
This accounts for only 52 weeks a year, leaving an extra day to be accounted for. As a result, a week is added to the fiscal calendar every five to six years.
Challenges in Power BI
Creation of Date Table
To represent a date table, it would be far easier to load the Date table from the source system like SAP. In our case, we used a SE38 program to extract the fiscal variants out of SAP.
A typical date table would have the following columns.
Please reach out to us if you need support in accommodating multiple fiscal periods or extracting data pertaining to the fiscal calendar from SAP.
Calculation of MTD or YTD
The standard time intelligence functions work only on either calendar year or fiscal calendars with fixed end dates.
For example, the DAX function TOTALYTD function Evaluates the year-to-date value of the expression in the current context. This function's fourth parameter takes in a literal string with a date that defines the year-end date. The default is December 31. So, these standard functions are not helpful as our fiscal calendar (5-4-4 fiscal variant) starts and ends on a different day every year.
Example below:
You can, as an alternate, use a custom DAX function to calculate the YTD or MTD. Example below:
MTD Calculation
Unit Sold (MTD) =
CALCULATE(
SUM('Fact Sales'[Units Sold])
,'Dim Date'[Fiscal Month] = MAX('Dim Date'[Fiscal Month])
,'Dim Date'[Fiscal Year] = MAX('Dim Date'[Fiscal Year])
,'Dim Date'[Calendar Date] <= MAX('Dim Date'[Calendar Date])
)YTD Calculation
Unit Sold (YTD) =
CALCULATE(
SUM('Fact Sales'[Units Sold])
,'Dim Date'[Fiscal Year] = MAX('Dim Date'[Fiscal Year])
,'Dim Date'[Calendar Date] <= MAX('Dim Date'[Calendar Date])
)Results
This will result in the perfect calculation for MTD and YTD.
Summary
The special fiscal periods or variants will require special attention while building a Power BI solution. DAX provides several options to address them. We have presented one such option here. This pattern can be applied to other variants of the same like 4-5-4 and 4-4-5 calendar.
Please use the github link to download the data file and Power BI file below.