Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Split Start Date and End Date into Multiple Rows

I can have a simple table like this:

 

Start Date    End Date
1/1/20234/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
01/1/2023
11/2/2023
21/3/2023
......
1184/29/2023
1194/30/2023

 

However, what happens when I add a new column, with multiple people:

 

Person      Start Date    End Date
Bill1/1/20231/5/2023
Melinda2/10/20232/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
Bill1/1/2023
Bill1/2/2023
Bill1/3/2023
Bill1/4/2023
Bill1/5/2023
Melinda2/10/2023
Melinda2/11/2023
Melinda2/12/2023
Melinda2/13/2023
Melinda2/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

  • 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