topic Calculated Table : Expand into multiple Rows in DAX Commands and Tips

Calculated Table : Expand into multiple Rows

knowparthshah — Thu, 09 Jan 2020 10:05:25 GMT

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

Re: Calculated Table : Expand into multiple Rows

JarroVGIT — Thu, 09 Jan 2020 21:27:56 GMT

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! 🙂

Re: Calculated Table : Expand into multiple Rows

knowparthshah — Fri, 10 Jan 2020 11:41:09 GMT

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] )