Forum Discussion

jerryr125's avatar
jerryr125
Helper IV
1 year ago
Solved

Use the most recent values based upon a date

Hi - I am looking to create a measure to do the following:   1. Obtain the most recent values based upon the most recent date (if the Actual column is not null). 2. Upon obtaining the most recent ...
Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago
    Measure = 
var a = TOPN(1,Filter(ALLSELECTED(Table1234),[Actual]<>BLANK(),[Date],DESC)
return divide(sumx(a,[Actual]),sumx(a,[Target]))
lbendlin's avatar
lbendlin
Super User
1 year ago

Like this ?

 

Measure = 
var d = max(Table1234[Date])
var a = TOPN(1,Filter(ALLSELECTED(Table1234),[Actual]<>BLANK() && [Date]<=d),[Date],DESC)
return divide(sumx(a,[Actual]),sumx(a,[Target]))
  • jerryr125's avatar
    jerryr125
    Helper IV
    1 year ago

    Hi lbendlin  - I appreciate your assistance - thank you.

    Almost there - since I am using this in a Card I get an error 'The end of the output was reached'

    I only need the one value - this case - the 80%.

     

    any thoughts ? Jerry

    • lbendlin's avatar
      lbendlin
      Super User
      1 year ago
      Measure = 
var a = TOPN(1,Filter(ALLSELECTED(Table1234),[Actual]<>BLANK(),[Date],DESC)
return divide(sumx(a,[Actual]),sumx(a,[Target]))
      • v-aatheeque's avatar
        v-aatheeque
        Community Support
        1 year ago

        Hi jerryr125 ,

         If our response addressed by the community member for  your query, please mark it as Accept Answer and click Yes if you found it helpful.

         

        Should you have any further questions, feel free to reach out.

        Thank you for being a part of the Microsoft Fabric Community Forum!