Forum Discussion

abartozzi's avatar
abartozzi
Frequent Visitor
5 years ago
Solved

Get the last and previous value

Hi Community, 

 

have this easy one for you but i failed 😞

 

Have this data

 

what I need to exrtract is the latest MIN value of rate for the latest Refresh date and Latest -1 (so i can build up a graph that highlights the variation between the two). 

 

I tried with some lastday built in function but it wasn't accurate. 

 

Thanks for your help

 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  abartozzi ,

     

    Create 2 measures as below:

     

     

     

    MIN rate for the latest refresh date = 
var _date=CALCULATE(MAX('Table'[RefreshDate]),ALL('Table'))
Return
CALCULATE(MIN('Table'[Rate]),FILTER(ALL('Table'),'Table'[RefreshDate]=_date&&'Table'[Checkin]=MAX('Table'[Checkin])))
    MIN rate for the latest refresh date = 
var _date=CALCULATE(MAX('Table'[RefreshDate]),ALL('Table'))
Return
CALCULATE(MIN('Table'[Rate]),FILTER(ALL('Table'),'Table'[RefreshDate]=_date&&'Table'[Checkin]=MAX('Table'[Checkin])))

     

     

     

    And you will see:(Here I changed the values of the rate on the previous latest date to test the result)

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

7 Replies

    • abartozzi's avatar
      abartozzi
      Frequent Visitor
      5 years ago

      Hi Alison, 

       

      thank you for your feedback. 

       

      I pasted some data here 

       

      What I need to get to ultimately would be a bar chart where

       

      x axis is checkin date

      y axis is rate 

      values are a MIN rate for the latest refresh date and the Min rate for the one before the last (clearly 2 different bars). 

       

      So I can spot variations. 

       

      Thanks

      Alberto 

      • v-kelly-msft's avatar
        v-kelly-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi  abartozzi ,

         

        The link of the data you provided is not public,pls modify it to be public for access.

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my post as a solution!