Creating table in DAX with appended rows

[gurujeet]

I have a table that looks like this:

Year, Amount A, Amount B, Amount C

I'd like to create a new table like:

Year, Amount

where for existing Year's, Amount=Amount A (i.e. starting table less Amount B and Amount C and Amount A renamed Amount), 

and add rows with Year = MAX(Year)+1 and Amount= Amount B where Year=MAX(Year),

and add rows with Year = MAX(Year)+2 and Amount= Amount C where Year=MAX(Year)

Any suggestions, thanks?

[v-jingzhang · Accepted solution]

Hi @gurujeet 

Assume your original table is called 'Table', you can create a new table with below DAX code.

Table 2 = 
var maxYear = MAX('Table'[Year])
var table1 = SELECTCOLUMNS('Table',"Year",'Table'[Year],"Amount",'Table'[Amount A])
var table2 = {(maxYear+1,MAXX(FILTER('Table','Table'[Year]=maxYear),'Table'[Amount B]))}
var table3 = {(maxYear+2,MAXX(FILTER('Table','Table'[Year]=maxYear),'Table'[Amount C]))}
return
UNION(table1,table2,table3)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[gurujeet]

Here is a sample input table:

Year    Amount A    Amount B    Amount C
2019     100               200              300
2020     110               210              310
2021     120               220              320

...and the desired output table:

Year    Amount
2019     100
2020     110
2021     120
2022     220
2023     320

[mahoneypat]

Please share a simple mock table of data (in a copy/paste format, not an image) along with desired output.  This will help you get a specific solution.

Pat