Measure to Calculate difference between values in existing column row by row

Question

Below is my table

My table name is VARIANCE_19_20

BELOW IS MEASURE I AM TRYING, BUT DIDN'T WORK.

VARIANCE = 
VAR NextIndex = VARIANCE_19_20[INDEX] - 1
RETURN
    VARIANCE_19_20[TC Count]
        - CALCULATE (
            VALUES ( VARIANCE_19_20[TC Count] ),
            FILTER ( ALL ( VARIANCE_19_20), VARIANCE_19_20[INDEX] = NextIndex)
        )

I am trying to write a measure to see difference in Variance column as shown in above table.

Please help

Hi Rado, thanks for the response. I am trying to create measure so my below formulae work to create up and down arrows. Key= if((VARIANCE_19_20[variance]>0,UNICHAR ( 128315 ), UNICHAR ( 9650 )) — Raj

StelioK StelioK · Accepted Answer · 2019-06-20T15:06:06

Here is one solution; create a calculated column using the following logic:

Delta =
VAR ShiftedIdx = VARIANCE_19_20[Index] - 1
VAR CurrentValue = VARIANCE_19_20[TC Count]
RETURN
    IF (
        VARIANCE_19_20[Index] = 1,
        0,
        CALCULATE (
            SUM ( VARIANCE_19_20[TC Count] ),
            FILTER ( VARIANCE_19_20, VARIANCE_19_20[Index] = ShiftedIdx )
        ) - CurrentValue
    )

Which will produce the following results:

Measure to Calculate difference between values in existing column row by row

1 Answers