Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Getting negative value, help

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 
Need Help

2 Replies

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    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's avatar
      v-xicai
      Community Support
      7 years ago

      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