Forum Discussion

Wickin's avatar
Wickin
Frequent Visitor
8 years ago
Solved

Calculate difference between two rows by using Index column

Hi al,

 

I know this question has been asked before, but I tried them all and wasn't able to compete. Sorry!

 

I've got the below table (4 colums) within Power BI (just created a table in Excel to edit values). I would like to calculate the difference between values in column "Kilometers".

The calculation should only be applied for rows with the same value for column "Reference", so I guess we need to calculate the difference between a row and a row where [Index] = ([Index] -1) ?

 

Anybody got any tips on the DAX or Power Query code to be applied? I've created a column "Expected result" in Excel, just to clarify things.

 

Thanks in advance!

 

Table1

Need Help
  • Stachu's avatar
    Stachu
    8 years ago

    try this

    Column =
VAR Index = 'Table'[Index]
VAR Reference = 'Table'[Reference]
VAR PrevKilometers =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[Kilometers], TRUE () ),
        FILTER ( 'Table', 'Table'[Index] = Index - 1 && 'Table'[Reference] = Reference )
    )
RETURN
    IF (
        ISBLANK ( PrevKilometers ),
        BLANK (),
        'Table'[Kilometers] - PrevKilometers
    )

9 Replies

  • Stachu's avatar
    Stachu
    Community Champion
    8 years ago

    try this

    Column =
VAR Index = 'Table'[Index]
VAR Reference = 'Table'[Reference]
VAR PrevKilometers =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[Kilometers], TRUE () ),
        FILTER ( 'Table', 'Table'[Index] = Index - 1 && 'Table'[Reference] = Reference )
    )
RETURN
    IF (
        ISBLANK ( PrevKilometers ),
        BLANK (),
        'Table'[Kilometers] - PrevKilometers
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      How can I adjust your formula to include data also as a criteria ?

      i.e.,compare the values between current timestamp and previous time stamp,say 17-08-2019,10:25 P.M and 17-08-2019,10:30 P.M.

      I have an ID Field and the glucose values,I should be capable of comparing the glucose_value,every now and then to indicate the episodes or spike in glucose level.

      Thanks

      Stachu 

      • Stachu's avatar
        Stachu
        Community Champion
        6 years ago

        the ID can be the same for multiple dates?
        if yes then the difference in glucose for a given ID should be something like this:

        Column =
VAR __ID = 'Table'[ID]
VAR __DateTime = 'Table'[DateTime]
VAR __PreviousDateTime =
    CALCULATE (
        MAX ( 'Table'[DateTime] ),
        FILTER ( 'Table', 'Table'[ID] = __ID && 'Table'[DateTime] < __DateTime)
    )
VAR __PreviousGlucose = 
    CALCULATE (
        MAX ( 'Table'[Glucose] ),
        FILTER ( 'Table', 'Table'[ID] = __ID && 'Table'[DateTime] = __PreviousDateTime)
    )
RETURN
    IF (
        ISBLANK ( __PreviousDateTime ),
        BLANK (),
        'Table'[Glucose] - __PreviousGlucose
    )
    • usmanaziz's avatar
      usmanaziz
      Regular Visitor
      6 years ago

      based on the formula provided below i have done on my data set but error is coming. kinldy guide me urgently. 

       

      • usmanaziz's avatar
        usmanaziz
        Regular Visitor
        6 years ago

        I want to know the incremental number of column 
        Death Cases, confirmed Cases,Recovered CAses, Active Cases.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Wickin

     

    The image attached is not visible.

    It would be great if you provide the Excel with dummy data.

     

    Regards,

    Suguna.