Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
I have a Period table with Star Date and End Date columns. Periods are not overlap each other, so each date is related to certain Period.
Period | Start Date | End Date |
period 1 | 01/01/2020 | 01/03/2020 |
period 2 | 01/07/2020 | 01/08/2020 |
I'd like to create a new table (like a Calendar or Date), with a columns Date and Period.
Please help me to write expression which creates the table as below.
Date | Period |
01/01/2020 | period 1 |
01/02/2020 | period 1 |
01/03/2020 | period 1 |
01/07/2020 | period 2 |
01/08/2020 | period 2 |
Solved! Go to Solution.
Perhaps:
Calendar Table =
ADDCOLUMNS(
CALENDAR(DATE(2020,1,1),DATE(2020,12,31)),
"Period",MAXX(FILTER('Table',[Start Date]<=[Date] && [End Date]>=[Date]),[Period])
)
Try
Create a date table
Date = calendar(Min(Period[Start Date]),Max(Period[End Date]))
Add a new column in that
Period = minx(filter(period, Period[Start Date]<=Date[Date] && Period[End Date]>=Date[Date]),Period[Period])
Thank you @amitchandak and @Greg_Deckler !!!
@amitchandak, your detailed sample helped me to understand Greg's solution which I like more as it gives a table from one expression.
Perhaps:
Calendar Table =
ADDCOLUMNS(
CALENDAR(DATE(2020,1,1),DATE(2020,12,31)),
"Period",MAXX(FILTER('Table',[Start Date]<=[Date] && [End Date]>=[Date]),[Period])
)
Sure, if you have dates in your data, you can use MIN and MAX or use CALENDARAUTO but not sure what your data looks like. If you have posted the sum total of your data, you will need to parse out the beginning and ending dates of your periods.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
20 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |