Forum Discussion
Merging Reporting Periods Into a Calendar Table
Hi, hoping someone can help as I've tried a few ways to approach this but am not getting anywhere.
I have a standard calendar table in Power BI and also a list of reporting periods in a separate excel which I'm bringing through which show some bespoke dates that are calculated within the excel:
| Reporting Period | Start | End |
| Period 1 | 01/04/2024 | 25/05/2024 |
| Period 2 | 26/05/2024 | 06/09/2024 |
| Period 3 | 07/09/2024 | 14/11/2024 |
| Period 4 | 15/11/2024 | 02/02/2025 |
I want to have a field in the calendar table (using Power Query, not calculated columns or a measure as I need to do further work once I have it) which shows, for each date, what period it falls in (anything before period 1 should default to period 1 and anything after period 4 should be null).
Does anyone have a solution as to how I can do this?
In Power Query.
Assuming your reporting period table looks like...
add a column that creates a list of dates from the start date to and including the end date.
List.Dates([Start], Number.From([End]-[Start])+1, #duration(1,0,0,0))remove the Start and End columns
expand the created column to new rows.
You should now have a table with two columns, Reporting Period and the coresponding dates.Merge the reporting period query into the date query using the date columns as the keys.
Expand only the Reporting Period column from the resulting merge.
Fill up so any dates prior to the first reporting period are 'Period 1'
And the result should be what you are looking for.
4 Replies
In Power Query.
Assuming your reporting period table looks like...
add a column that creates a list of dates from the start date to and including the end date.
List.Dates([Start], Number.From([End]-[Start])+1, #duration(1,0,0,0))remove the Start and End columns
expand the created column to new rows.
You should now have a table with two columns, Reporting Period and the coresponding dates.Merge the reporting period query into the date query using the date columns as the keys.
Expand only the Reporting Period column from the resulting merge.
Fill up so any dates prior to the first reporting period are 'Period 1'
And the result should be what you are looking for.If the period is flat/fixed you can just add a Custom Column writing a logic similar to this:
= if [Date] >= #date(2024,4,1) and [Date] <= #date(2024,5,25) then "Period 1" else if [Date] >= #date(2024,5,26) and [Date] <= #date(2024,9,6) then "Period 2" else if [Date] >= #date(2024,9,7) and [Date] <= #date(2024,11,14) then "Period 3" else if [Date] >= #date(2024,11,15) and [Date] <= #date(2025,2,2) then "Period 4" else nullSadly not - what you've suggested is what I'm doing as a manual workaround every time the periods change but the calculations that are done in the excel based on other data then determine and update the reporting period dates. Also, when a reporting period is over, it is deleted.
