topic Re: Pulling a value from a calculated date in DAX Commands and Tips

Pulling a value from a calculated date

drivas771994 — Mon, 30 May 2022 20:31:00 GMT

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.

Re: Pulling a value from a calculated date

Greg_Deckler — Mon, 30 May 2022 20:40:18 GMT

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.

Re: Pulling a value from a calculated date

drivas771994 — Mon, 30 May 2022 21:46:02 GMT

Greg,

Here's a sample dataset:

Athlete Name	Test Date	Test Value	Most Recent Body Mass Date	Most Recent Body Mass
Athlete 1	12/1/2021	100	12/7/2021	64KG
Athlete 1	2/1/2022	105	2/7/2021	66KG
Athlete 1	4/1/2022	110	3/27/20	68KG

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