Expand table with condition

[tanquoc0309]

I have 2 tables - invoice and Dimdate

InvoiceID	Period Start	Period End
1	29 Jun 2019	02 Jul 2019
2	1 Sep 2019	2 Sep 2019
3	4 Sep 2019	4 Sep 2019

How can I create a new table as below:

InvoiceID	Date	Period Start	Period End
1	29-Jun-19	29-Jun-19	2-Jul-19
1	30-Jun-19	29-Jun-19	2-Jul-19
1	1-Jul-19	29-Jun-19	2-Jul-19
1	2-Jul-19	29-Jun-19	2-Jul-19
2	1-Sep-19	1-Sep-19	2-Sep-19
2	2-Sep-19	1-Sep-19	2-Sep-19
3	4-Sep-19	4-Sep-19	4-Sep-19

[v-frfei-msft · Accepted solution]

Hi @tanquoc0309 ,

We can create a calculated table as below.

Table 2 = 
VAR a =
    ADDCOLUMNS (
        CROSSJOIN (
            'Table',
            CALENDAR ( MIN ( 'Table'[PeriodStart] ), MAX ( 'Table'[PeriodEnd] ) )
        ),
        "s", 'Table'[PeriodStart],
        "e", 'Table'[PeriodEnd]
    )
VAR ad =
    ADDCOLUMNS ( a, "c", IF ( [Date] >= [s] && [Date] <= [e], 1, BLANK () ) )
RETURN
    SELECTCOLUMNS (
        FILTER ( ad, [c] = 1 ),
        "id", 'Table'[InoviceID],
        "st", 'Table'[PeriodStart],
        "end", 'Table'[PeriodEnd],
        "Date", [Date]
    )

PBIX as attached.

[bharathvee]

Hi, can u post both the tables you have?