inner join filter with date

[Silver_75]

Hi
I have simple problem with inner join in Power BI Desktop. I want to convert my T-SQL in DAX but I can’t found a solution
My objective is to obtain “how many clients (ID) of 3 products buy again one specific product in the next 12 month on historical data”
My T-SQL is:

select COUNT(distinct b.id)
from sales a
inner join sales b on a.id=b.id and b.date_purchase>a.date_purchase
where (a.product in ('2', '3', '4') and b.date_purchase='30/03/2018' )
and b.product='4'
and b.date_purchase<=DATEADD(yy, 1, a.date_purchase )

I  wrote my DAX, but I’m losing in the filter context with date (????)
Could you help me?
Thanks

Clients next 12  = VAR cte  = CALCULATETABLE(
                  VALUES(sales[id]);
           
                  USERELATIONSHIP(sales[date_purchase];DimDate[Fulldate]);
                    FILTER (
        ALL ( sales[s2]; sales[s3]; sales[s4]);
        sales[product2]=1 || sales[product3]=1 || sales[product4]=1 ))
     
VAR cte2  = CALCULATETABLE(
                  VALUES(sales[id]); all(DimDate); USERELATIONSHIP(sales[date_purchase];DimDate[Fulldate]); FILTER(ALL(sales[product4];sales[date_purchase] ); sales[prouct4]=1 && ??????  ))

VAR joined = NATURALINNERJOIN(cte;cte2)
VAR result = COUNTROWS(joined)

RETURN result

Additional question is How many Clients in then next 12 month have bought  the product 4 at least 4 or more  time?

[v-eachen-msft]

Hi @Silver_75 ,

You need a DATEADD function:

sales[date_purchase]<=DATEADD(DimDate[Fulldate],1,YEAR)

For your second question, you could create a column to count numbers.

Column = 
var a = CALCULATE(SUM('sales[product4]),ALLEXCEPT(sales,sales[id]))
return
IF(sales[product4]=1,a)

 Then add it to the filter like [column]>=4

[Silver_75]

thank you, I've used

Clients next 12  = VAR cte  = CALCULATETABLE(
                  VALUES(sales[id]);
           
                  USERELATIONSHIP(sales[date_purchase];DimDate[Fulldate]);
                    FILTER (
        ALL ( sales[s2]; sales[s3]; sales[s4]);
        sales[product2]=1 || sales[product3]=1 || sales[product4]=1 ))
     
VAR cte2  = CALCULATETABLE(
                  VALUES(sales[id]); all(DimDate); USERELATIONSHIP(sales[date_purchase];DimDate[Fulldate]); FILTER(ALL(sales[product4];sales[date_purchase] ); sales[prouct4]=1 && sales[date_purchase] <=DATEADD( sales[date_purchase] ;1;YEAR ))

VAR joined = NATURALINNERJOIN(cte;cte2)
VAR result = COUNTROWS(joined)

RETURN result

but the result is underestimated respect to my T-SQL. I've