Calculate Difference Between the last non blank value and the second last nonblank value with filter

[Faith_Data]

Reg no	Mileage
CCN 342D	3342
CCN 455A	880
VWT 403B	10935
CCN 342D	4000
CCN 455A	1000

 Please help with the dax function to calculate difference between the last non blank row and the second last non blank row.The reg number is repeated.

[tamerj1]

Hi @Faith_Data 
Here you go https://we.tl/t-Mrey2dTPxa

First of all make sure the have the mileage column as intiger data type. 

Mileage diff measure - Tamer = 
SUMX ( 
    VALUES ( 'PM Data'[REG NO] ),
    CALCULATE (
        VAR LastTwoDatesTable = TOPN ( 2,'PM Data', 'PM Data'[LAST SERVICE], DESC )
        VAR Last_Date = MAXX ( LastTwoDatesTable, 'PM Data'[LAST SERVICE] )
        VAR Last_2ndDate = MINX ( LastTwoDatesTable, 'PM Data'[LAST SERVICE] )
        VAR Last_DateMileage = MAXX ( FILTER ( LastTwoDatesTable, 'PM Data'[LAST SERVICE] = Last_Date ), 'PM Data'[LAST MILEAGE] )
        VAR Last_2ndDateMileage = MAXX ( FILTER ( LastTwoDatesTable, 'PM Data'[LAST SERVICE] = Last_2ndDate ), 'PM Data'[LAST MILEAGE] )
        RETURN
            IF ( 
                COUNTROWS ( LastTwoDatesTable ) >= 2,
                Last_DateMileage - Last_2ndDateMileage
            )
    )
)

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Mileage diff measure: =
VAR lastmileage =
    MAX ( Data[Mileage] )
VAR previousofthelastmileage =
    MAXX (
        FILTER (
            ALL ( Data ),
            Data[Reg no] = MAX ( Data[Reg no] )
                && Data[Mileage] < lastmileage
        ),
        Data[Mileage]
    )
RETURN
    IF ( HASONEVALUE ( Data[Reg no] ), lastmileage - previousofthelastmileage )

[Faith_Data]

Thank you @Jihwan_Kim for faster response ,however the measure on my end is bringing all blanks

[Jihwan_Kim]

Hi,

Please share your sample pbix file's link, and then I can try to look into it to come up with a more accurate solution.

Thanks.

[Faith_Data]

Faith_Data v1.pbix

[tamerj1]

Hi @Faith_Data 
Please provide the expected results as per the sample data provided.