DAX Calculation - MAX(if for multiple criteiria - Most recent date with mileage over 1 mile for each

[balbuquerque]

Hello,

Can anyone please help me with the DAX logic I'm trying to formulate for my calculated column. I'm trying to create a new measure in the dataset to calculate the most recent date, where each of the 630 vehicles had mileage over 1 mile.  In other words, I want to know when was the the vehicle run last or what was the most recent date the vehicle has a daily mile over 1 mile.  The table below summarizes what the data looks like and what I want to accomplish.

The DAX formula I have is: Measure = MAX(if('TableName'[Miles])>1,'TableName'[Date]))

but it's obviosuly way off. Please help me, thank you.

A

Vehicle	Date	Mileage for each day	Total Lifetime Miles	Last Date in Service  (the correct forumula for Measure should output the following information for each vehicle)
1	1/1/2017	100	100	1/4/2017
1	1/2/2017	200	300	1/4/2017
1	1/3/2017	300	600	1/4/2017
1	1/4/2017	400	1000	1/4/2017
2	1/1/2017	101	101	1/3/2017
2	1/2/2017	102	203	1/3/2017
2	1/3/2017	103	306	1/3/2017
2	1/4/2017	0	306	1/3/2017
3	1/1/2017	201	201	1/2/2017
3	1/2/2017	202	403	1/2/2017
3	1/3/2017	0	403	1/2/2017
3	1/4/2017	0	403	1/2/2017
4	1/1/2017	301	301	1/1/2017
4	1/2/2017	0	301	1/1/2017
4	1/3/2017	0	301	1/1/2017
4	1/4/2017	0	301	1/1/2017

[Ashish_Mathur]

Hi,

Here's a calculated column formula i wrote

=CALCULATE(MAX([Date]),FILTER(Table1,[Vehicle]=EARLIER([Vehicle])&&[Mileage for each day]>0))

[erik_tarnvik]

VehicleMaxDate = 
      VAR V = MAX(Miles[Vehicle]) 
      RETURN 
          CALCULATE(MAX(Miles[Date]),FILTER(ALL(Miles),Miles[Vehicle] = V && Miles[Milage] > 1))

[balbuquerque]

I tried using the above syntax but failed. The error says my EquipmentNum is incorrect. Please help.

Measure 2 =
      VAR V = MAX(OperatingStatistic[StatValue][EquipmentNum])
      RETURN
          CALCULATE(MAX(OperatingStatistic[StatValue][StatDate]),FILTER(ALL(OperatingStatistic[StatValue]),OperatingStatistic[StatValue][EquipmentNum] = V && OperatingStatistic[StatValue][StatMeter] > 1))

[mede]

Is EquipmentNum a number or a string?

If it is a string (contains some kind of text), then you need to wrap value with quotation marks ""

For example:

If string:

OperatingStatistic[EquipmentNum] = "V"

If number:

OperatingStatistic[EquipmentNum] = 5

Make sure 

[erik_tarnvik · Accepted solution]

Hi @balbuquerque,

I am guessing somewhat here because I don't truly know the structure of your data nor the set-up of the visual your are creating, but please try the below. 

Measure 2 =
      VAR V = MAX(OperatingStatistic[EquipmentNum])
      RETURN
      CALCULATE(MAX(OperatingStatistic[StatDate]),
                FILTER(ALL(OperatingStatistic),
                OperatingStatistic[EquipmentNum] = V && OperatingStatistic[StatMeter] > 1))