Forum Discussion
Merging Reporting Periods Into a Calendar Table
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 null
Sadly 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.