Filter fact table by two date dimensions at the same time (using USERELATIONSHIP)

[Anonymous]

Hi, I have an Orders fact table that has an order date and a shipped date keys that are related to the same Date dimension table. 

The order date is the active relationship and the shipped date is inactive. 

I can get a count of orders by order date using:

Order Count = COUNTROWS(Orders)

And a count by shipped date using:

Shipped Order Count = CALCULATE([Order Count], USERELATIONSHIP(Order[ShipedDateKey], 'Date'[DateKey])

But how can I show orders that were placed and shipped in the same period? How can I use both relationships at the same time?

Another important point is that DateKey and ShippedDateKey are real keys and not dates. So I can't filter based on those columns directly. 

Thank you for your help!

[Ashish_Mathur]

Hi,

Ideally the relationship should be between proper date columns.  Also, what do you mean by "show orders that were placed and shipped in the same period"?  Share some data, define same period and show the expected result.

[amitchandak]

@Anonymous ,

Try a measure like

placed and shipped in the same period =
var _min = maxx(allselected(date),date[date])
var _max = maxx(allselected(date),date[date])
return
calculate([ Order Count ], filter(Order, Order[ShipedDateKey]<=_max && Order[ShipedDateKey]>-_min))

[Anonymous]

Thank you. Unfortnately, my date keys are actual keys and not date values. Is there a way to use USERELATIONSHIP inside a filter statement?