Returning Last Date of "Use" with DirectQuery report

[jignaski18]

I am trying to generate the last date of use when time used > 0, for a list of assets within a direct query. The data is structured as follows:

I would like to generate different measures to create the following tables:

The primary measure I need to solve for is 'Last date used'. My current measure yields this result:

Last Date Used = 
VAR FirstVisibleOrder =
    MIN ( 'Pump Gear Usage'[Last Stage ID])
VAR PreviousOrder =
    CALCULATE (
        MAX ( 'Pump Gear Usage'[Last Stage ID]),
        REMOVEFILTERS ( Dates[Date] ),
        'Pump Gear Usage'[Last Stage ID] < FirstVisibleOrder,
        'Pump Gear Usage'[Time In Gear] <>0
    )
VAR FilterPreviousOrder =
    TREATAS (
        {
            PreviousOrder
        },
        'Pump Gear Usage'[Last Stage ID]
    )
VAR Result =
    CALCULATE (
        MAX('Pump Gear Usage'[End time]),
        REMOVEFILTERS ( Dates[Date]),
        FilterPreviousOrder
    )
RETURN
    Result

[jignaski18 · Accepted solution]

@lbendlin thanks for leading me to the solution. This dax ended up working: 

LDU = 
var d = SELECTEDVALUE('Pump Gear Usage'[Pump_Name])
var d2 = CALCULATE(LASTDATE('Pump Gear Usage'[End time]),'Pump Gear Usage'[Pump_Name]=d,'Pump Gear Usage'[Pump Hours]<>0)
VAR LDU  = CALCULATE(MAX('Pump Gear Usage'[End time]),FILTER('Pump Gear Usage','Pump Gear Usage'[End time]<d2 && 'Pump Gear Usage'[Pump_Name]=d))

RETURN
LDU

[lbendlin]

I would define "Last Date Used"  thusly:

LDU =

var d=SELECTEDVALUE(Table1[End time])

return CALCULATE(max(Table1[End time]), Table1[End time]<d)

(or use MAXX if you don't like CALCULATE)

Then the Days since last used is the difference from the current date.

Now I have a question on "Days idle".  Let's say a pump was used on 3/1 and on 3/3.  The date difference is 2, but shouldn't the "days idle"  be only 1 ?

Days idle = DATEDIFF([LDU],min(Table1[End time])-1,DAY)

[lbendlin]

Please provide sample data in usable format (not as a picture).

[jignaski18]

File