Indexing yearly subscriptions

[mrleijzer]

Hello everyone,

I have a pretty hard to explain situation which I'd like to solve, I'll give it a go:

I have 3 tables:

1. customer information (ID, name, address, etc.)
2. yearly maintenance work for this customer, these are contracts for 15 years so this table has 15 rows for each customer (ID from customer, year, costs of the year)
3. an index table to calculate the yearly index on the yearly subscription for the customer (year, index %, year is related to the startyear + 1)

So let's say I have a customer and to keep it simple this example has only 3 years of subscription.

Name	Year	Costs
Customer 1	2021	4000
Customer 1	2022	1000
Customer 1	2023	1000

So the total costs are 7000, the customer will pay a yearly amount in a spread of the 3 years: 6000 / 3 = 2000 (index excluded)

And let's say my index table looks like this

Year	Index
2020	2%
2021	3%
2022	3%
2023	4%

Now, the first year has no index, so indexing the subscription only starts from the second year.

How can I produce a table calculating the amount of money that must be paid by the customer, index included?

It would look like this (the amount is calculated from the amount of last year + index):

Name	Year	Amount	Index
Customer 1	2021	2000,00	0%
Customer 1	2022	2060,00	3%
Customer 1	2023	2142,40	4%

I hope I've explained clear enough, thanks in advance for anyone's time and effort.

[mrleijzer]

Thanks, with this I found a workaround!

[Anonymous · Accepted solution]

HI @mrleijzer,

Current power bi does not support recursion calculation in Dax formula. You can take a look at the following link about Dax recursion to know more about these:

Previous Value (“Recursion”) in DAX – Greg Deckler

Regards,

Xiaoxin Sheng