Split Start Date and End Date into Multiple Rows

[calcstuff]

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

[OwenAuger · Accepted solution]

Hi @calcstuff 

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