How to multiple the last value with an increasing factor in DAX

[Anonymous]

I'd want to take the most recent value of Amount, which is sorted by date, then multiply it by increasing rate factor. For example, I'd like to know how much the Amount is increasing by 5% per day.

Date	Amount
1/01/2021	24651
2/01/2021	54835
3/01/2021	21364
4/01/2021	12465
5/01/2021	4878
6/01/2021	1235
7/01/2021	256363
8/01/2021	215687
9/01/2021	7895
10/01/2021	1210
11/01/2021	56460
12/01/2021	48790
13/01/2021	1544
14/01/2021	78456

For instance, I anticipate the following Amount (15/1/2021) to be 78456 + 78456*0.05 = 82378.8, followed by (16/1/2021) 82378.8 + 82378.8 * 0.05 = 86497.74... and so on.

How can I do that in DAX please?

[amitchandak · Accepted solution]

@Anonymous , A measure with help from date table

measure = 
var _max = maxx(allselected(Data), Data[Date]) 
var _amt = calculate(lastnonblankvalue('Date'[Date], sum(Data[Amount])), allselected())
return if(Max('Date'[Date]) <=_max, sum(Data[Amount]), _amt* power(1.05, datediff(_max, Max('Date'[Date]), Day)+1) )

file attached after signature

[Anonymous]

Hi Amit,

Thank you so much for your help. This works perfectly. One question though, it does not work when I try to slice it by date, giving me blank value. How can I achive that with an existing measure? I appreciate your help. 🙂