calculate Daily Change Delta

[majdkaid22]

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,

[v-yulgu-msft · Accepted solution]

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

[scrat57]

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