expanding dates in a period and add those dates to the rows

[pouryas7]

Hello,

In my table, I have Start_Date and EndDate. I want to expand the dates in between.

Let's say:

ID    Start_date     End_Date

1     2018/01/01   2018/01/03

I want this:

ID  Date

1    2018/01/01

1    2018/01/02

1    2018/01/03

I tried to use this DAX function (Add column feature in dashboard):

DATESINPERIOD( DimDate[FullDate],[START_DATE],[NUM_DAYS],DAY)

But, it gave me this error:

A table of multiple values was supplied where a single value was expected.

Well, I have a table named DimDate and NUM_DAYS is the number of days between start and end date.

Thanks!

[OwenAuger · Accepted solution]

Hi @pouryas7

To create this table using DAX, you would have to click New Table rather than New Column.

Adding a column can only add a column using a scalar expression, preserving the number of rows.

To create this expanded table, you would need to create a table with an expression like this (assuming DimDate contains all required dates):

Expanded Table =
SELECTCOLUMNS (
    GENERATE (
        ExistingTable,
        DATESBETWEEN (
            DimDate[FullDate],
            ExistingTable[Start_date],
            ExistingTable[End_Date]
        )
    ),
    "ID", ExistingTable[ID],
    "Date", DimDate[FullDate]
)

Alternatively, you could use the CALENDAR function to generate the sequences of dates without relying on DimDate:

Expanded Table =
SELECTCOLUMNS (
    GENERATE (
        ExistingTable,
        CALENDAR ( ExistingTable[Start_date], ExistingTable[End_Date] )
    ),
    "ID", ExistingTable[ID],
    "Date", [Date]
)

Regards,

Owen