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 t...
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
    )
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
4 years ago

Hello Mr. Stachu,
I tried the above formula and it returned the incorrect values. All the valules in the column are taking the first value as a reference. Please find the attached image for the reference.