Custom fiscal calendar with pre-defined date ranges

[gbrunnerbi]

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

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.

Custom fiscal dates column

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

🙂

[eisbaer99]

This is absolutely perfect for my reporting but I need to know how to do the following please;

Current Month Offset

Current Day Offset

Both would need to feed from the 'End' date. 

If I run this from the resulting CustomFiscalDate then the offset runs from the last day of the month each time and not the actual period end date.

Thanks

[amitchandak · Accepted solution]

Can you request Admin @JCM_iTalent and post it as a blog.

[JCM_iTalent]

@gbrunnerbi If you would like to post to the Community Blog, please use the Message Admins button in the blog section.