Forum Discussion
Anonymous
2 years agoNot applicable
Split Start Date and End Date into Multiple Rows
I can have a simple table like this:
| Start Date | End Date |
| 1/1/2023 | 4/30/2023 |
and with the following DAX I can generate a new table with rows between the two dates, on a per day basis:
DateSplitTable =
VAR _StartDate = MIN('YourTable'[Start Date])
VAR _EndDate = MAX('YourTable'[End Date])
VAR _DateDiff = DATEDIFF(_StartDate, _EndDate, DAY)
RETURN
ADDCOLUMNS(
GENERATESERIES(0, _DateDiff, 1),
"SplitDate", _StartDate + [Value]
)
and here's the output, just as I expect:
| Value | SplitDate |
| 0 | 1/1/2023 |
| 1 | 1/2/2023 |
| 2 | 1/3/2023 |
| ... | ... |
| 118 | 4/29/2023 |
| 119 | 4/30/2023 |
However, what happens when I add a new column, with multiple people:
| Person | Start Date | End Date |
| Bill | 1/1/2023 | 1/5/2023 |
| Melinda | 2/10/2023 | 2/14/2023 |
How can I take my original DAX and modify it to group by person, then split the rows? The output should look like this:
| Person | SplitDate |
| Bill | 1/1/2023 |
| Bill | 1/2/2023 |
| Bill | 1/3/2023 |
| Bill | 1/4/2023 |
| Bill | 1/5/2023 |
| Melinda | 2/10/2023 |
| Melinda | 2/11/2023 |
| Melinda | 2/12/2023 |
| Melinda | 2/13/2023 |
| Melinda | 2/14/2023 |
Hi Anonymous
Here's what I would recommend.
I've tweaked the code to use GENERATE/CALENDAR to expand the list of dates:
DateSplitTable = VAR ExpandDates = GENERATE ( YourTable, CALENDAR ( YourTable[Start Date], YourTable[End Date] ) ) RETURN SELECTCOLUMNS ( ExpandDates, "Person", YourTable[Person], "SplitDate", [Date] )Does this work for you?
Regards
1 Reply
- OwenAugerSuper User
Hi Anonymous
Here's what I would recommend.
I've tweaked the code to use GENERATE/CALENDAR to expand the list of dates:
DateSplitTable = VAR ExpandDates = GENERATE ( YourTable, CALENDAR ( YourTable[Start Date], YourTable[End Date] ) ) RETURN SELECTCOLUMNS ( ExpandDates, "Person", YourTable[Person], "SplitDate", [Date] )Does this work for you?
Regards