Forum Discussion

abeinke2018's avatar
abeinke2018
Frequent Visitor
7 years ago
Solved

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 on...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
abeinke2018
Frequent Visitor
7 years ago

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's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      abeinke2018
      Frequent Visitor
      7 years ago

      Thankyou for this, it worked well