Create Lower Triangular Matrix

[hwoehler]

Hello everybody,

I would like to implement the following:

I have a DateTable in Power BI, a table "FSE" with data, and then a filtered Table table. This contains the dates of the last date of the year (relative to "FSE") and the corresponding value. I would like to visualize a lower triangular matrix that looks like this: (The color formatting is unimportant. The calculations/values ​​are important)

desired result

In Excel I did this manually, the files are attached (pbix and excel file): https://we.tl/t-x5Zi1P1Zw1
For Power BI I have not been successful so far. Can someone help me?

Best regards, hwoehler

Information about calculations:

The calculation works according to the formula: new value = ((final value / initial value) ^(1 / n)) - 1

The divisor (initial value) is fixed per column (the year on the x-axis). It is a constant (per column!). The dividend (= the final value) increases by 1 until it reaches the year 2018 (maximum of year). The final value is therefore flexible. The exponent also changes the value n (= the duration in years). This increases logically over the years. Columnwise, one line is always started later, as before.

How the calculations work, you can see on the pictures here: https://we.tl/t-hEQNrFJ9FS

[parry2k]

@hwoehler what is the conditional formatting based on?

[parry2k]

@hwoehler this is how it looks like in Power BI, just need to conditional format it

[hwoehler]

Hello @parry2k,

this is exactly what i need. So how do you solve it?

Regards, hwoehler

[parry2k · Accepted solution]

@hwoehler solution is attached, the core of it is following DAX measure

Value = 
VAR __yearonColumn = CALCULATE( MAX ( FSE[Year Column] ), ALLEXCEPT( FSE, FSE[Year] ) )
VAR __cYear =MAX ( DateTable[Year] )
VAR __pYear = __cYear - 1
VAR __prevYear = 
 CALCULATE( 
    [Parv Last Value of Year],
    ALL ( DateTable[Year]),  FSE[Year] = __pYear )
VAR __currYear =
 CALCULATE( 
    [Parv Last Value of Year],
    ALLEXCEPT( FSE, FSE[Year] )
)
VAR __identity =  __yearonColumn - __pYear 
RETURN
IF ( __identity > 0 && __prevYear <> BLANK(),
    (
        DIVIDE( __currYear, __prevYear ) ^ 
        DIVIDE( 1, __identity )
    ) - 1
)

[parry2k]

@hwoehler I think this is what you are looking for, it is from power bi