topic Re: Creating a calculated measurement with data from different tables and days in DAX Commands and Tips

Creating a calculated measurement with data from different tables and days

drivas771994 — Sun, 29 May 2022 19:31:46 GMT

Hi,

I'm trying to create a calculation where I can create a ratio between bodyweight and performance in exercises

I currently have two seperate tables. One with bodyweights that are collected on a weekly basis and another where there exercise weights put in on a daily basis. The main issue I have is that the dates don't line up well for example: I have a weight recorded on Monday 5/23/22 and then the exercise was done on Wednesday 5/25/22. Is there a way I can get power bi to find the weight from the nearest date and create a bodyweight ratio to the weight moved?

Re: Creating a calculated measurement with data from different tables and days

Anonymous — Sun, 29 May 2022 21:14:06 GMT

Hello

First create a date table and link both tables.

Second create this measure

weight=

var dateweight=selectedvalue(weight(date))

return

calculate(min(exercice[Date]), exercice[Date]>=dateweight)

Re: Creating a calculated measurement with data from different tables and days

drivas771994 — Sun, 29 May 2022 21:29:55 GMT

I ran it and it is giving me the date from which the most recent bodyweight is. The date is accurate, I need the actual bodyweight. Here was my dax formula:

Most Recent Body Mass =

var dateweight=selectedvalue(SPARTA_SCANS2[Date])

return

calculate(min(TESTS[Date]), TESTS[Date]>=dateweight)