Forum Discussion
calculate Daily Change Delta
Hi Community,
am trying to get the Delta value (Daily change) of a column in my db.
The following Formula
Floating Profit_USD Delta = 'live_daily'[Floating Profit_USD] - CALCULATE(SUM('live_daily'[Floating Profit_USD]), 'live_daily'[TIME]=EARLIER('live_daily'[TIME])-1)is working fine when it's week days, but when when it's Friday to Monday, am getting the value incorrect. as seen in the highlighted cell below
I understand this is because of the -1 at the end, but I cannot figure out how to put "LASTDAY" or LASTNONBLANK into this statement, so the calculate take on the lastnonblank day
Appreciate if somebody can assist on the formula
Thanks,
Hi majdkaid22,
In Query Editor mode, sort the [TIME] column in ascend sequence, then, add an index column. Then, modify above formula to:
Floating Profit_USD Delta = 'live_daily'[Floating Profit_USD] - CALCULATE(SUM('live_daily'[Floating Profit_USD]), 'live_daily'[Index]=EARLIER('live_daily'[Index])-1)Regards,
Yuliana Gu
2 Replies
Hi majdkaid22,
In Query Editor mode, sort the [TIME] column in ascend sequence, then, add an index column. Then, modify above formula to:
Floating Profit_USD Delta = 'live_daily'[Floating Profit_USD] - CALCULATE(SUM('live_daily'[Floating Profit_USD]), 'live_daily'[Index]=EARLIER('live_daily'[Index])-1)Regards,
Yuliana Gu
Hello
I try to do it with my table but i have an error. (A single value for column 'Parc' in table cannot be determined...)
If i put a "SUM" before 'Delta Parc Fibre'[Parc], i have an error on earlier who doesn't exist (i suppose it is the problem for the first row ?)
My measure :
'Delta Parc Fibre'[Parc] - CALCULATE(sum('Delta Parc Fibre'[Parc]); 'Delta Parc Fibre'[index Semaine]=EARLIER('Delta Parc Fibre'[index Semaine] )-1)My table :
index Semaine is modeling as a whole number.
What do i made wrong ?
Bruno
