Calculated Table : Expand into multiple Rows

[knowparthshah]

I have to write Dax to make a calculated table which should collect all distinct years from date table and create something like this:

2018 2017

2018 2018

2019 2018

2019 2019

2020 2019

2020 2020

2021 2020

2021 2021

Please help, thanks

I want to have a calculated table

[JarroVGIT · Accepted solution]

Well this was a fun question to answer because I had to think of a strategy to get 2 rows per unique year in the date table. 

Anyway, I found a solution for you. I have a date table (DateTable2) from 1/1/2018 till 31/12/2022. THen I create the following calculated table for it:

CalcTable = 
VAR _DateTableWIthYears = ADDCOLUMNS(DateTable2, "Year", YEAR([Date]))
VAR _rankedTable = ADDCOLUMNS(_DateTableWIthYears, 
    "RankWithinYear", RANKX(FILTER(_DateTableWIthYears, [Year] = EARLIER([Year])), [Date]))
VAR _filterTopTwoPerYear = FILTER(_rankedTable, [RankWithinYear] <3)
VAR _addPrevYear = ADDCOLUMNS(_filterTopTwoPerYear, "SecondColumn", 
    IF([RankWithinYear] = 1, [Year], [Year] -1))
RETURN
SELECTCOLUMNS(_addPrevYear, "Year1", [Year], "Year2", [SecondColumn])

This is not optimized but I wrote it like you could easily follow the logic behind it. It results in the following table:

Let me know if this works for you! 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[JarroVGIT · Accepted solution]

Well this was a fun question to answer because I had to think of a strategy to get 2 rows per unique year in the date table. 

Anyway, I found a solution for you. I have a date table (DateTable2) from 1/1/2018 till 31/12/2022. THen I create the following calculated table for it:

CalcTable = 
VAR _DateTableWIthYears = ADDCOLUMNS(DateTable2, "Year", YEAR([Date]))
VAR _rankedTable = ADDCOLUMNS(_DateTableWIthYears, 
    "RankWithinYear", RANKX(FILTER(_DateTableWIthYears, [Year] = EARLIER([Year])), [Date]))
VAR _filterTopTwoPerYear = FILTER(_rankedTable, [RankWithinYear] <3)
VAR _addPrevYear = ADDCOLUMNS(_filterTopTwoPerYear, "SecondColumn", 
    IF([RankWithinYear] = 1, [Year], [Year] -1))
RETURN
SELECTCOLUMNS(_addPrevYear, "Year1", [Year], "Year2", [SecondColumn])

This is not optimized but I wrote it like you could easily follow the logic behind it. It results in the following table:

Let me know if this works for you! 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[knowparthshah · Accepted solution]

Hi @JarroVGIT ,

Thanks for the answer.

Even I figured out a way to acheive this. 

DateCYPY =
SELECTCOLUMNS (
    ADDCOLUMNS (
        GENERATEALL (
            VALUES ( 'Date'[Year] ),
            DATATABLE ( "type", STRING, { { "m" }, { "m-1" } } )
        ),
        "visibleY", IF ( [type] = "m", VALUE ( 'Date'[Year] ), 'Date'[Year] - 1 )
    ),
    "SelectedYear", 'Date'[Year],
    "VisibleYear", [visibleY]
)