Comparing the total increase of two different odometers on the same vehicle

[zudar]

Hi all,

I could really use some help constructing the right DAX-measure(s) for the following case..

I want to compare two different odometers for a number of vehicles. In order to make a fair comparison, the increase on the two different odometers should be of a period that starts on the same date and ends on the same date. I'm dealing with four different tables.

1) Vehicles:

VehicleID	Model
1	A
2	A
3	B
etc..	etc..

2) Calendar:

Calender = CALENDAR ( DATE ( 2000, 1, 1 ), NOW() )

So it looks like:

Date

...

1-1-2022

2-1-2022

3-1-2022

etc..*

*the calendar range is from 1-1-2000 until today with one date-value for each day

3) Odometer X

VehicleID*	Date	Value
1	21-12-2021	4
1	29-12-2021	26
1	2-1-2022	59
1	8-1-2022	80
1	18-1-2022	122
1	3-2-2022	151

*For simplification purposes, I'm only showing the data for one VehicleID

4) Odometer Y

VehicleID*	Date	Value
1	18-12-2021	2
1	29-12-2021	24
1	4-1-2022	61
1	12-1-2022	89
1	18-1-2022	125
1	2-2-2022	149

*For simplification purposes, I'm only showing the data for one VehicleID

These tables are connected like:

Vehicles[VehicleID] = 'Odometer X'[VehicleID]

Vehicles[VehicleID] = 'Odometer Y'[VehicleID]

Calendar[Date] = 'Odometer X'[Date]

Calendar[Date] = 'Odometer Y'[Date]

My desired end result is a table like this, with a row per VehicleID:

VehicleID	Matching Start Date	Matching End Date	X-increase	Y-increase	Difference
1	29-12-2021	18-1-2022	(122 - 26) -> 96	(125 - 24) -> 101	(101 - 96) -> 5
...	...	...	...	...	...

If there's no match in either the Start Date or End Date I would like the table to return 'No Match' for all columns.

I hope this all makes sense. If something is unclear, or you would like to have a .pbix-file of this data, please let me know.

Your help is very much appreciated. Thank you!

[johnt75 · Accepted solution]

You could create a series of measures along the lines of

X Increase =
VAR CurrentVehicle =
    SELECTEDVALUE ( 'Vehicle'[Vehicle ID] )
VAR XDates =
    CALCULATETABLE ( VALUES ( 'Odometer X'[Date] ) )
VAR YDates =
    CALCULATETABLE ( VALUES ( 'Odometer Y'[Date] ) )
VAR SharedDates =
    INTERSECT ( XDates, YDates )
VAR Result =
    IF (
        COUNTROWS ( SharedDates ) < 2,
        "No match",
        VAR StartDate =
            MIN ( SharedDates )
        VAR EndDate =
            MAX ( SharedDates )
        VAR XStartValue =
            LOOKUPVALUE (
                'Odometer X'[Value],
                'Odometer X'[Date], StartDate,
                'Odometer X'[Vehicle ID], CurrentVehicle
            )
        VAR XEndValue =
            LOOKUPVALUE (
                'Odometer X'[Value],
                'Odometer X'[Date], EndDate,
                'Odometer X'[Vehicle ID], CurrentVehicle
            )
        RETURN
            XEndValue - XStartValue
    )
RETURN
    Result

[zudar]

When I try this solution on my actual data, I get an error message "The MIN function only accepts a column reference as an argument"..

EDIT: When I replace MIN(...) with FIRSTDATE(...), and MAX(...) with LASTDATE(...), the proposed solution works!

Thank you! 🙂 

[johnt75]

You could also use MINX(SharedDates, [Date])