cancel
Showing results for
Did you mean:
Regular Visitor

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

 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.

1 ACCEPTED SOLUTION
Super User

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 )
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

6 REPLIES 6
Community Champion

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
)
)
)``````

Super User

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 )
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Regular Visitor

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

Super User

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Regular Visitor
Community Champion

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

Announcements