Date Closest to a date AND within a window of time

[Allisond]

I have a table of weights and dates and need to find a weight within a window of time (- 25 to -35 days)   AND then find the Wt that is closest to the actual 30 day date.   I can easily find the max weight within that window of time, but am struggling with how I would remove the Max and filter for the value closest to the actual 30 day mark.

ID                               Date                       WT

123                            12/01/20                 150

123                             11/29/20                 152

123                            12/5/20                     151.5

123                             1/1/21                     151

Return for the 1/1//21 Date would be 150   (12/1/20 wt).      Falls in the 30 day =/-5 days and is closest to actual 30 days.  

Any help would be so appreciated. 

[v-jayw-msft · Accepted solution]

Hi @Allisond ,

Create a date table and use it as silicer. Check the measures.

Measure = IF(SELECTEDVALUE('Table 2'[Date])<=SELECTEDVALUE('Table'[Date]),BLANK(),ABS(ABS(DATEDIFF(SELECTEDVALUE('Table 2'[Date]),SELECTEDVALUE('Table'[Date]),DAY))-30))

Measure 2 = 
var min_ = MINX(FILTER('Table',[Measure]<>BLANK()),[Measure])
return
IF(ISBLANK(min_)||min_>24,BLANK(),CALCULATE(SUM('Table'[WT]),FILTER('Table',[Measure]=min_)))

Best Regards,

Jay

[lbendlin]

Nah, this won't work. You need to describe how to break ties when (for example) one weight was done on 12/1/20 and another one on 12/3/20.  Why not intrapolate between the two weights ?

Also what should happen when there was no weight taken in the -35..-25 timeframe?

[Allisond]

Blank if no weight in the window.  

What do you mean by intrapoloate between 2 weights? -- sorry somewhat of a newbee here.

[lbendlin]

Say you have 150 on 12/1 and 153 on 12/4. Draw a line between them and you get 151 for 12/2.