Forum Discussion

drivas771994's avatar
drivas771994
Icon for Helper II rankHelper II
4 years ago

Pulling a value from a calculated date

Hi,

 

I'm trying to compare some bodyweights to some strength exercises and am having trouble pulling the bodyweight from a seperate table. I currently have two tables. One with bodyweights from weigh ins and one with exercise data. I've gotten my formula to find the most recent date but don't know how to pull the actual weight from that day's weigh in.  Can someone help me? The test value is the exercise data and I just need the body mass associated with the date listed in the table

 

Here is the current formula I have:

 

Most Recent Body Mass =

var dateweight=selectedvalue(SPARTA_SCANS2[Date])

return

calculate(min(TESTS[Date]), TESTS[Date]>=dateweight)
 
Also here is the view of it in my table. 

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    drivas771994 Perhaps:

    Measure =
  VAR __dateweight = MAX('SPARTA_SCANS2'[Date])
  VAR __Athlete = MAX('SPARTA_SCANS2'[Athlete])
  VAR __BMDate = FILTER('TESTS',[Athlete] = __Athlete && [Date] >= __dateweight ),[Date])
RETURN
  MAXX(FILTER('TESTS',[Athlete] = __Athlete && [Date] = __BMDdate),[Value])

    Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • drivas771994's avatar
      drivas771994
      Icon for Helper II rankHelper II
      4 years ago

      Greg,

      Here's a sample dataset:

       

      Athlete NameTest DateTest Value

      Most Recent Body Mass Date

      Most Recent Body Mass

      Athlete 1

      12/1/2021

      		100

      12/7/2021

      64KG

      Athlete 1

      		2/1/20221052/7/202166KG

      Athlete 1

      		4/1/20221103/27/2068KG

       

      The red highlighted values are my expected output. Testing values exist in my "test" table and my bodyweights exist in my scans table. They don't always occur on the same date and i'd like power bi to find the closest bodyweight to the testing date. Sorry I'm pretty new to this