Forum Discussion

tanquoc0309's avatar
tanquoc0309
Helper II
6 years ago
Solved

Expand table with condition

I have 2 tables - invoice and Dimdate

 

InvoiceIDPeriod StartPeriod End
129 Jun 201902 Jul 2019
21 Sep 20192 Sep 2019
34 Sep 20194 Sep 2019

 

How can I create a new table as below:

InvoiceIDDatePeriod StartPeriod End
129-Jun-1929-Jun-192-Jul-19
130-Jun-1929-Jun-192-Jul-19
11-Jul-1929-Jun-192-Jul-19
12-Jul-1929-Jun-192-Jul-19
21-Sep-191-Sep-192-Sep-19
22-Sep-191-Sep-192-Sep-19
34-Sep-194-Sep-194-Sep-19

 

Need Help
Tips and Tricks
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    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.

     

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    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.