Lookup Max Value between date/time for each day

[abeinke2018]

Hi, this has been extremely frustrating as I am simply trying to return the last value for each day 

I have a dynamic calendar which Im trying to return the value to

I can do a max lookup but only returns the first value for the NEXT day (Not the last value for the current day) if i could put 0.9 there instead of 1, then I could get this to work

A better formula would be apreciated though

Thankyou

Diesel Balance (L) = (LOOKUPVALUE(Balances[Diesel],Balances[TimeStamp],(MAX(Balances[TimeStamp]))+1))

I want to return the value in yellow

[v-lili6-msft]

hi,@abeinke2018

     After my research, you can use this formula:

Diesel Balance (L) = (LOOKUPVALUE(Balances[Diesel],Balances[TimeStamp],CALCULATE(MAX(Balances[TimeStamp]),FILTER(Balances,Balances[Date]=EARLIER(Balances[Date])))))

Result:

Best Regards,

Lin

[abeinke2018]

Hi Lin, this looks great, the only problem is the last part of the formula ... EARLIER(Balances[Date] returns an error as per below

[v-lili6-msft · Accepted solution]

hi,@abeinke2018

     That formula is to create a column and if you want to create a measure, you can try to use this formula:

Measure = LOOKUPVALUE(Balances[Diesel],Balances[TimeStamp],CALCULATE(MAX(Balances[TimeStamp]),ALLEXCEPT(Balances,Balances[Date])))

Result:

Best Regards,

Lin

[abeinke2018]

Thankyou for this, it worked well