Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 )
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.
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
)
)
)
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.
Thank you @Jihwan_Kim for faster response ,however the measure on my end is bringing all blanks
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.
Hi @Faith_Data
Please provide the expected results as per the sample data provided.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |