Group members in Power Bi

Question

Please Consider this table:

 Year        Amount
 -------------------
 2010        10000
 2011        12300
 2012        14000
 2013        13000
 2014        22000
 2015        10000

I want to create a table in Power BI like this:

 Year        Amount
 -------------------
 2010        10000
 2011        12300
 2012        14000
 2013        13000
 2014        22000
 2015        10000
 Last2Years  32000

I have two questions:

1) How can I group two memberss and place it in original dimension?

2) Can it be dynamic that group every two last years? For example if 2016 add to dimension then my group is 2015+2016.

Thanks

Answer 1

In DAX there is not any way to add a row to an existing table, you must duplicate the table using a union with a row containing your measure.

A measure to get amount of the last two years:

AmountLastTwo = SUMx(TOPN(2;'Table';'Table'[Year]);[Amount])

TOPN(2.. will make sure the measure always contain the last two years.

Then you can create a new table using UNION between your original table and a row containing the measure like this:

UnionTable = UNION('Table'; ROW("Year";"LastTwoYears";"Amount";[AmountLastTwo]))

Year            Amount
2010            10000
2011            12300
2012            14000
2013            13000
2014            22000
2015            10000
LastTwoYears    32000

You add a table from this button and then paste the code from above:

Another option is to do this in Power Query.

1. Duplicate your table and name it SUM_LAST_TWO
2. Sort by year and then select keep the top rows (using the UI)
3. Sum the amount (using the UI)
4. Create an empty query (new source > empty query) and add this to the formula field:= #table({"Year", "Amount"}, {{"Last2Years",#"SUM_LAST_TWO"}}). This will create a one-row table with the sum over the last two years.
5. In your originial table, append the one-row table you just created (using the UI)

However, if it is not really necessary to have this extra row (that breaks the data type) in your table I would recommend using only the measure AmountLastTwo.