Merging Reporting Periods Into a Calendar Table

[KatieH]

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?

[jgeddes · Accepted solution]

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. 

[KatieH]

Thank you @jgeddes - that is exactly what I wanted and this works perfectly!

[ray_aramburo]

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

[KatieH]

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.