Matrix Table Value Changes of Consecutive Years

[ChrisPBI]

Hello,

what I try to implement is a Matrix Table with value differences for consecutive years.

Example:

The first row, and the first column mirror each other. In this case, they show the years. The table is filled on the example of:  2008 = 1, 2009 = 2, and 2010 = 3. It is read from left to top or reverse. Reading from left to top shows the chronological order. The diagonal shows dashes because they would only compare the same years with one another. From 2008 to 2009 there is a difference of 1 (2009 = 2 minus 2008 = 1). From 2008 to 2010 there is a difference of 2 (2010 = 3 minus 2008 = 1). From 2009 to 2010 there is a difference of 1 (2010 = 3 minus 2009 = 1). This approach would proceed. Below the diagonal the values change their signs since the view goes in the opposite direction. For instance from 2009 to 2008 the difference is -1 (2008 = 1 minus 2009 = 2).

My data model is:

My table structure is:

Is that possible?

Thanks and Regards,

Chris

[v-ljerr-msft · Accepted solution]

@ChrisPBI

---

what I try to implement is a Matrix Table with value differences for consecutive years.

Is that possible?

---

Yes, it is possible to do that.

First create a table with the Year information and the corresponding Value, here I create a table called 'TestTable'

Second, create a duplicate table of 'TestTable'(with different column names), called 'TestTable2', with column Year2 and Value2.

Then use CROSSJOIN to create another table 'TestTable3'.

TestTable3 = CROSSJOIN(TestTable,TestTable2)

Use the following formula to create a custom column [Difference] for 'TestTable3'.

Difference = TestTable3[Value2]-TestTable3[Value]

Last, use 'TestTable3' to create the Matrix Table.

Note: the values in matrix can only accept numeric values so that we can’t replace 0 with dash “-”.