cancel
Showing results for
Did you mean:
abeinke2018 Frequent Visitor

## Lookup Max Value between date/time for each day

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

1 ACCEPTED SOLUTION

Accepted Solutions Community Support Team

## Re: Lookup Max Value between date/time for each day

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4 Community Support Team

## Re: Lookup Max Value between date/time for each day

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
abeinke2018 Frequent Visitor

## Re: Lookup Max Value between date/time for each day

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

## Re: Lookup Max Value between date/time for each day

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
abeinke2018 Frequent Visitor

## Re: Lookup Max Value between date/time for each day

Thankyou for this, it worked well