cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
abeinke2018 Frequent Visitor
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))

 

 

1320.jpgI want to return the value in yellow

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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:

13.PNG

 

 

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
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:

1.PNG

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
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

 

1324.jpg

Community Support Team
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:

13.PNG

 

 

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
Frequent Visitor

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

Thankyou for this, it worked well