Forum Discussion
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
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
4 Replies
v-lili6-msftCommunity Support
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
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
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
