cancel
Showing results for
Did you mean:
Regular Visitor

## calculating yearly budget adjusted for inflation

Hi All,

I'm trying to calculate a yearly budget that is adjusted for yearly inflation.
The budget is based on a calculation (Sum("Budgetcolumn")) and the inflation percentages are based on a table :

 index Year percentage 1 2018 0,045 2 2019 0,045 3 2020 0,045 4 2021 0,045 5 2022 0,045 6 2023 0,045 7 2024 0,045 8 2025 0,045

To make checking my calculations easier the current inflation percentages are al the same, in the definitive model the percentages will be changed to different numbers.

I would like to calculate what my budget needs to be in 2025 with the inflation adjustment, in Excel I would calculate that like this:

 A B C D 1 Year Percentage Budget 2 2017 4,50% € 100.000,00 3 2018 4,50% € 104.500,00 4 2019 4,50% € 109.202,50 5 2020 4,50% € 114.116,61 6 2021 4,50% € 119.251,86 7 2022 4,50% € 124.618,19 8 2023 4,50% € 130.226,01 9 2024 4,50% € 136.086,18 10 2025 4,50% € 142.210,06

For 2017 i'd just fill in the budget manualy as it is the starting budget. However for the following years the formula would be:
2018 = D2+D2*C3
2019 = D3+D3*C4
Etc.

This of course will not work in Dax and after a full day searching trough google i can't seem to find a working method.
This could be because English is not my first language and i'm just not using the correct search terms...

In any case i would greatly appreciate somebody help me out!

Best regards,

Hendrik

1 ACCEPTED SOLUTION
Super User

You can use PRODUCTX() for that.

Calculated column:

``````Budget =
var r = 'Table'[Row]
return 100000*COALESCE(PRODUCTX(filter('Table','Table'[Row]<r),1+'Table'[Percentage]/100),1)``````

See attached.

2 REPLIES 2
Super User

You can use PRODUCTX() for that.

Calculated column:

``````Budget =
var r = 'Table'[Row]
return 100000*COALESCE(PRODUCTX(filter('Table','Table'[Row]<r),1+'Table'[Percentage]/100),1)``````

See attached.

Regular Visitor

works perfectly, thank you so much!

Announcements

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

#### Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!