I have spent an hours trying to find formula for calculating cumulative inflation (CPI index) … reversed in the time … according to the years. In Power BI. And it seems that it did not exist on the Internet till today?

That seems total incredible!

Yes, I have found lot of formulas for cumulative counting, but all of them had the common problems. Most of them consist ordinary „SUM“ function, which is not usable for calculating cumulative inflation (CPI index). They are good for calculating sum of sales etc., but not the inflation (CPI index). The second problem is, that when they does not have „SUM“ function, than they are not reversed. They do not calculace backwards in time. From today back to the history. And when they are reversed, than they does not calculate according to years. But according to diverse increasing numbers like here, etc.

The closest solution which i have found is here. But as above … it is not reversed. But when I adjust the formula a little bit, then it works perfectly:

Cumulative =
EXP(
SUMX(
FILTER(Inflation, Inflation[Year]>=EARLIEST(Inflation[Year])),
LN(1 + Inflation[Inflation])
)
)

You can try both formulas (original not adjusted and adjusted) to compare what they exactly do.

Enjoy!

PS: Kdyby to někdo hledal v češtině, tak je to jak v Power BI počítat zpětnou kumulativní inflaci? 🙂

Komentáře nejsou povoleny.