Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

gbrunnerbi
Advocate I
‎03-24-2020 03:14 PM

Custom Fiscal Calendar with Pre-Defined Date Ranges

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:

 

custom fiscal periods.jpg

 

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.

 

fiscal dates.png

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:

aftertransform.png

 

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

 

🙂