LookupValue - Max Date - Same Table issues

[Gkr1981]

Hi All,

I am using PowerBI desktop, and have an API bringing through data, and i have made some calculation columns i manage to get me a new specific lookup.

I have a table which comes through with individual shift information in it an example is below.

Current Shift Name	Load Time	Previous Shift Name
01-10-2020-Day Shift	01/10/2020 6:30AM	30-09-2020-Night Shift
01-10-2020-Day Shift	01/10/2020 7:30AM	30-09-2020-Night Shift
01-10-2020-Day Shift	01/10/2020 8:30AM	30-09-2020-Night Shift
01-10-2020-Night Shift	01/10/2020 6:30PM	01-10-2020-Day Shift
01-10-2020-Night Shift	01/10/2020 11:50PM	01-10-2020-Day Shift
01-10-2020-Night Shift	02/10/2020 4:30AM	01-10-2020-Day Shift
02-10-2020-Day Shift	02/10/2020 7:00AM	01-10-2020-Night Shift

What i am trying to do, is add a column to this table called [PREVIOUS SHIFT FINAL LOAD] which looks up [Previous Shift Name] and returns the max value of [Dump Time]

Example would be rows 4/5/6 where [Shift Name] = 01-10-2020-Night Shift the new Column [PREVIOUS SHIFT FINAL LOAD] would be populated with 01/10/2020 8:30AM

Every time I am adding this its returning the Max Value based on filtering Shift Name, and Not for the previous shift name.

Last Load Previous Shift =
MAXX(
    KEEPFILTERS(VALUES('TruckCycles'[Previous Shift Name])),
    CALCULATE(MAX('TruckCycles'[Load Time]))
)

Is what im using but it doesnt give me the actual value for the Previous Shift Name, only the [Current Shift Name]

Any help would be appreciative.

Cheers

 

[Bo_Ry]

Please try

=Maxx(FILTER(TruckCycles,TruckCycles[Current Shift Name]=EARLIER([Previous Shift Name])),TruckCycles[Load Time])

 

[Gkr1981]

Please try

=Maxx(FILTER(TruckCycles,TruckCycles[Current Shift Name]=EARLIER([Previous Shift Name])),TruckCycles[Load Time])

Thanks for this, it has worked great, however i have noticed i have missed something completely from here

I have an additional column called [Loader] and i need the above filter to be also linked to that field

I need it to be if(AND(TruckCycles[Current Shift Name]=EARLIER([Previous Shift Name], TruckCycles[Loader]=TruckCycles[Loader])

Hopefully something like that works?

Cheers

 

[Bo_Ry]

Try

=Maxx(FILTER(TruckCycles, And(TruckCycles[Current Shift Name]=EARLIER([Previous Shift Name]),TruckCycles[Loader]=EARLIER(TruckCycles[Loader]))),TruckCycles[Load Time])