The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Solved! Go to 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.
Proud to be a Super User! | |
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.
Proud to be a Super User! | |
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
Proud to be a Super User!
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
81 | |
62 | |
54 |
User | Count |
---|---|
246 | |
119 | |
114 | |
87 | |
70 |