topic Re: Calculate Difference Between the last non blank value and the second last nonblank value with filter in DAX Commands and Tips

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

Faith_Data — Tue, 24 May 2022 09:54:32 GMT

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.

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

tamerj1 — Tue, 24 May 2022 10:11:48 GMT

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

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

Jihwan_Kim — Tue, 24 May 2022 10:13:00 GMT

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 )

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

Faith_Data — Tue, 24 May 2022 12:32:15 GMT

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

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

Jihwan_Kim — Tue, 24 May 2022 12:49:32 GMT

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.

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

Faith_Data — Tue, 24 May 2022 14:13:57 GMT

Faith_Data v1.pbix

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

tamerj1 — Wed, 25 May 2022 07:52:46 GMT

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