DAX Generate statement to self join a table

[Anonymous]

Hi,

I'm trying to join a date table to itself using a >= operator.  The date table contains Period End Dates, and contains only a single column, which is the DateKey in integer format (ex: 20190131).  The combined date table should contain two columns.  The first column is the original DateKey, and the second column is each DateKey that is >= the original date key.  Ex: Let's say the table contains three Period End Dates: 20190131, 20181231, and 20181130.  The combined date table would look like this:

DateKey1     DateKey2

20190131     20190131

20190131     20181231

20190131     20181130

20181231     20181231

20181231     20181130

20181130     20181130

If they were two separate date tables, I could use a GENERATE statement to achieve the result.  This works:

GENERATE(DateTable1, SUMMARIZE(FILTER(DateTable2,DateTable2[DateKey] <= DateTable1[DateKey]),DateTable2[DateKey]))

However, if I just reference DateTable1, I get an error that the GENERATE does not allow two columns with the same name:

GENERATE(DateTable1, SUMMARIZE(FILTER(DateTable1,DateTable1[DateKey] <= DateTable1[DateKey]),DateTable1[DateKey]))

I'm looking for suggestions about how to code this statement without needing two versions of the table.

Thanks for any help.

[LivioLanzo · Accepted solution]

Hello @Anonymous

try this:

Table = 
GENERATE(
    Data,
    SELECTCOLUMNS(
        CALCULATETABLE(
            VALUES( Data[DateKey] ),
            Data[DateKey] >= EARLIER( Data[DateKey] )
        ),
        "DateKey2", Data[DateKey]
    )
)

[Anonymous]

Thank you - that worked