How to get the nearest date from a filtered table

[Anonymous]

I have a merged table with all my data with programmed purchases and date of purchase. I want to create a measure for the variance but first i need a variable that gets the nearest programmed purchase to the date of purchase.

ID	Description	Date
CS	Programmed	2/2/2023
CS	Purchase	2/2/2023
CS	Programmed	3/10/2023
CS	Programmed	5/9/2023
CS	Purchase	4/14/2023
CJ	Programmed	1/30/2023
CI	Programmed	4/28/2023
CJ	Purchase	1/30/2023
CJ	Programmed	6/9/2023
CJ	Purchase	5/19/2023
CI	Purchase	5/19/2023
CI	Purchase	6/20/2023

I want that for each purchase the measure looks for the nearest programmed purchase for that ID so I can calculate the difference in days and then average by ID

[Anonymous]

For each purchase I want to look for the nearest programmed date for that ID, which means that for

CS

Purchase

2/2/2023

the answer would be 

CS

Programmed

2/2/2023

because its the nearest

but for 

CS

Purchase

4/14/2023

it would be 

CS

Programmed

5/9/2023

because that is nearest to 4/14/23 than 3/10/23.

and that calculation would be done for every purchase, then substract the date of purchase to the date programmed

so it would be 

=abs((2/2/23-2/2/23)+(4/14/23-5/9/23))=25

and average that by the amount of purchases

=25/2

=12.5 is the average of days between programmed dates and purchases dates

[ThxAlot · Accepted solution]

Avg Nearest.pbix

Nothing too fancy,

[Anonymous]

I think it works great but what is the "@nearest" is it a variable? why isn't it at the beginning with var?

[TomMartens]

Hey @Anonymous ,

"@nearest" is only the name of a calculated column that is added by using ADDCOLUMNS to a virtual table, here this table is initially created by using SUMMARIZE.
The @ is often used by DAX developers to differentiate natural table columns from virtual columns.

Hopefully, this helps to understand @ThxAlot  great answer even better.

Regards,

Tom

[Anonymous]

ID	Description	Date
CS	Programmed	2/2/2023
CS	Purchase	2/2/2023
CS	Programmed	3/10/2023
CS	Programmed	5/9/2023
CS	Purchase	4/14/2023
CJ	Programmed	1/30/2023
CJ	Purchase	1/30/2023
CJ	Programmed	6/9/2023
CJ	Purchase	5/19/2023
CI	Purchase	5/19/2023
CI	Purchase	6/20/2023
CI	Programmed	4/28/2023
CI	Programmed	6/21/2023

I added a line in the table for better understanding, but the answer would be...

ID	Average difference in days
CS	12.5
CJ	10.5
CI	27

[TomMartens]

Hey @Anonymous,

you neither explained how the result is calculated, for more than ID, next, you also did not answer my question regarding your data model.

Take the time and answer the questions.

Regards,

Tom

[TomMartens]

Hey @Anonymous ,

describe the expected result based on the data in the above table.

Next, does your sample data reflect a table in a data model with a dedicated  calendar table or is your solution. based on a single table? 

Regards,

Tom