Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Solved! Go to 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 , 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
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. 🙂
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
84 | |
66 | |
62 | |
60 |
User | Count |
---|---|
197 | |
118 | |
108 | |
78 | |
69 |