Forum Discussion

heejinyune's avatar
heejinyune
Frequent Visitor
3 years ago
Solved

Find the nearest value within the past month

Hello community! 

 

Is anyone familiar with writing the Dax Programming to find the nearest (closest ) value in the table?

 

For example, My table is like this:

 

And I want to find the nearest Model built time value with 1 month average built time (in this case, today date is 2022/11/16 so 

(213 + 444 + 532 + 111 / 4 ) = 325 ) 

so the nearest value of 325 in this table should be 213 (A125) not 345 (A124) as that model not in a past month from current date.

Therefore, how do i return 213 using the dax programming?

 

Thank you so much for your help! 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    3 years ago

    Hi, heejinyune ;

    You could create a measure as follow:

    near = 
var _aver=
AVERAGEX(FILTER(ALL('table'),EOMONTH([Model Built Date],0)=EOMONTH(TODAY(),0)),[Model built time (Mins)])
return  
MINX(SUMMARIZE(FILTER(ALL('table'),EOMONTH([Model Built Date],0)=EOMONTH(TODAY(),0)),[Model ID],"1", ABS( SUM([Model built time (Mins)])-_aver)),[Model ID])

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, heejinyune ;

    You could create a measure as follow:

    near = 
var _aver=
AVERAGEX(FILTER(ALL('table'),EOMONTH([Model Built Date],0)=EOMONTH(TODAY(),0)),[Model built time (Mins)])
return  
MINX(SUMMARIZE(FILTER(ALL('table'),EOMONTH([Model Built Date],0)=EOMONTH(TODAY(),0)),[Model ID],"1", ABS( SUM([Model built time (Mins)])-_aver)),[Model ID])

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.