Anyone having issues using USERELATIONSHIP and filters?

[id013]

So I have a table that shows product sales. There is a purchase date and a return date as well as a field that states whethere they are within 30 days of purchase date, there is also a "Within 30 Days" slicer that I can use to filter the data by that field. So I have 2 relationships between my sales table and the date dimension:

purchase_date_key = date_key

return_date_key = date_key

The primary relationship is purchase date 

So I have 2 measures:

count_purchases := CALCULATE(DISTINCTCOUNT(order_id),ISBLANK(purchase_date)=FALSE())

count_returns := CALCULATE(DISTINCTCOUNT(order_id),ISBLANK(purchase_date)=FALSE(),ISBLANK(return_date)=FALSE(),USERELATIONSHIP(return_date_key,date_key)

Both seem to work fine but when I want to count the number of returns within 30 days of purchase the filter doesn't seem to work properly.

If I do:

CALCULATE(DISTINCTCOUNT(order_id),ISBLANK(purchase_date)=FALSE(),ISBLANK(return_date)=FALSE(),USERELATIONSHIP(return_date_key,date_key,FILTER(sale,within_30_days=TRUE)) it returns blank (it shouldn't)

If I do:

CALCULATE(DISTINCTCOUNT(order_id),ISBLANK(purchase_date)=FALSE(),ISBLANK(return_date)=FALSE(),within_30_days=TRUE,USERELATIONSHIP(return_date_key,date_key) it returns the right count but when I set the within_30_days slicer to FALSE the count doesn't decrease. 

Is there another way I can accomplish this? 

[Anonymous]

Hi @id013 ,

It's hard to find the solution only by these formulas. Can you show some sample data to us if you don't have any Confidential Information?

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[amitchandak]

Not sure I got it but if you want to move one the day +/- 30 days try like

-30 = CALCULATE(SUM(purchase[Amount]),dateadd('Date'[Date],-30,day),userelation(..))

+30 = CALCULATE(SUM(purchase[Amount]),dateadd('Date'[Date],30,day),userelation(..))

If you want to use some kind clause with both dates, refer :https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos.

[id013]

hi @amitchandak 

Unfortunately I don't have control over the fields and the within_30_days field is actually just a True\False value that gets updated. 

[amitchandak]

What exactly you want to do with that 30 day filter. Can you explain with example