I wanted to share my solution for creating a completely custom fiscal calendar in Power BI:
Assume your client has a pre-defined logic for creating their fiscal periods, like in the screenshot below:
How will you link this to your DAX date table in the front end?
I think the easiest way is the following:
1) Transform your date columns to whole number.
2) Create a custom column with the formula below.
This will produce a list of all numbers between all dates in both columns.
3) Expand the newly created FiscalDatesColumn “to new rows”, so we end up with all dates within the respective ranges.
4) Now convert your number columns back to date format.
You will now have all dates within all your custom fiscal date ranges – you can delete the Start and End columns since we do not need them anymore.
Your new table will look like this:
You can now set up a relationship between this table and your DAX date table in the Power BI front-end in order to gain full filter functionalities.
Feel free to comment or ask questions
🙂
Be the first to comment