How to calculate datediff with many to many relationships

[HUAM]

Hi, all

I don't know how to calculate date difference value..I tried lookupvalue function, but it didn't work..

Here is the relationship.

I want to find a date difference value when the date is the same or larger like a red box.

Finally, I want to count the case where the difference is within 30 days based on the coupon_usedate.

Thank you

[v-yangliu-msft]

Hi  @HUAM ,

Here are the steps you can follow：

1. Create measure.

max_table =
var _1=CALCULATE(MAX('Table'[coupon_usedate]),FILTER(ALL('Table'),'Table'[coupon_consumerid]=MAX('Table'[coupon_consumerid])))
var _2=CALCULATE(MAX('Table2'[lastlinkdate]),FILTER(ALL('Table2'),'Table2'[item]=MAX('Table'[coupon_consumerid])))
return
ABS( DATEDIFF(_2,_1,DAY))

Flag =
IF([max_table]<=30,1,0)

Count_measure =
CALCULATE(DISTINCTCOUNT('Table'[coupon_consumerid]),ALLSELECTED('Table'))

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@HUAM , is possible to have bridge table or common column to get that

refer datediff

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

[HUAM · Accepted solution]

But I don't know..
this measure value is no work

DATEDIFF('v_dim_coupon'[coupon_useddate], 'item2'[date], DAY)

I want to count rows like this
countrows(
calculatetable(
'v_dim_coupon',
FILTER('v_dim_coupon', 'v_dim_coupon'[type] = "A", 'v_dim_coupon'[name] = "B",
DATEDIFF('v_dim_coupon'[coupon_useddate], 'item2'[date], DAY) <=30),
FILTER('item2', 'item2'[status] = "Active")
)
)

But I still don't know how to calculate.
Thank you