# LookupValue - Max Date - Same Table issues



## Gkr1981 (Oct 29, 2020)

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 NameLoad TimePrevious Shift Name01-10-2020-Day Shift01/10/2020 6:30AM30-09-2020-Night Shift01-10-2020-Day Shift01/10/2020 7:30AM30-09-2020-Night Shift01-10-2020-Day Shift01/10/2020 8:30AM30-09-2020-Night Shift01-10-2020-Night Shift01/10/2020 6:30PM01-10-2020-Day Shift01-10-2020-Night Shift01/10/2020 11:50PM01-10-2020-Day Shift01-10-2020-Night Shift02/10/2020 4:30AM01-10-2020-Day Shift02-10-2020-Day Shift02/10/2020 7:00AM01-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 (Oct 30, 2020)

Please try

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


----------



## Gkr1981 (Nov 2, 2020)

Bo_Ry said:


> 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 (Nov 3, 2020)

Try

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


----------

