Assume your client has a pre-defined logic for creating their fiscal periods, like in the screenshot below:
How will you connect this to your DAX date table?
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:
Using {}
we can create a list in PowerQuery(M)
This will generate a list of all numbers between all dates in both columns.
3) Expand the newly created CustomFiscalDates column "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 the relationship between CustomFiscalDates and your Date column of your Calendar table.
List generation in PowerQuery {} can be helpful in a lot of scenarios.
This post was originally published in the Power BI Forum on 03/24/2020 link