Calculating % change from a matrix table

[mrothschild]

Link to PBIX file is: https://drive.google.com/file/d/1j_4CrUj0fPh0xK6hnO7OZ-uIWkoP5Kdg/view?usp=sharing

I'm trying to take the following Matrix output and calculate the natural log, or LN([value (t)]/[value (t-1)] ^ (1 / ([year (t) - [year (t-1)] for each given observation except for the first chronologically in a given row (because there is no (t-1) value).  You'll also note from the table that (t-1) isn't always available, in which case, I'd want (t-[most recent data point prior]) and the associated year. To complicate issues further, what's shown below is for a single model type.  But when the data is less sliced, the Values in the Matrix are unit-weighted (via a Measure), and so I'd like to keep that unit-weighting as part of the calculation base, rather than just using Values.

From that calculation, I want to calculate the average and standard deviation on a row-by-row basis of the output.  A screenshot of the desired output from Excel is shown below the Power BI Matrix screen shot.

[Anonymous · Accepted solution]

HI @mrothschild,

It sounds like you are trying to recursion calculation to invoke the previous result of the current expression, right? If that is the case, you can use iterator functions to achieve cumulative calculation but power bi does not support recursion calculations.

Previous Value (“Recursion”) in DAX - Microsoft Power BI Community

Regards,

Xiaoxin Sheng