Getting negative value, help

[Anonymous]

Hey!

I am trying to calculate the consumed energy, by calculating current energy minus previous (from TotalEnergy_MWh), since it is an accumulating number, with following formula (see data below): 

EnergyPer15min_kWh = 
VAR device = Data[Device]
VAR d = Data[Rank]
VAR preDate = d-1
VAR energyPreDate = 
SUMX(
FILTER(
Data,
Data[Rank] =preDate
&& Data[Device] = device
), 
Data[TotalEnergy_MWh]
)
RETURN
(Data[TotalEnergy_MWh] - energyPreDate)*1000

I am getting many EnergyPer15min that are negative, and I don'y want them to be negative, and I think it depends on my Rank Column, that it don't go in order, in this picture I have sorted time by Desc, and to one Data device, which I want the Ranking to consider, this is my Ranking formula:

Rank = RANKX(FILTER(Data, Data[Device] = EARLIER(Data[Device])), Data[Index], ,ASC)

Why am I getting negative values, why is the Ranking not coming in order? Anybody that knows this?

Kind regards 

Agnes 

[v-xicai]

Hi @Anonymous ,

You can change the Rank in  DAX below.

Rank = RANKX(FILTER(Data, Data[Device] = EARLIER(Data[Device])), Data[TotalEnergy_MWh], ,ASC)

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-xicai]

Hi  @Anonymous  ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case.

Best regards

Amy Cai