HELP to solve this condition

[Sdaniel]

ok, so I got two dates: 1. Purchase Date 2. Sell Date these two columns come from Table1 and then I have 1 main table for date.
I need to add a filter so I can filter one date and see all products purchased before this date and all products sold after that date. i tried to make a relationship but the relation can not be built because of the two dates Purchase date and Sell date these two columns can not be related to one date.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

One of many ways to solve this is to have one active relationship and one inactive relationship.

count by purchase date / count by sell date

Order count by purchase date: = 
COUNTROWS(order_fact)

USERELATIONSHIP function (DAX) - DAX | Microsoft Learn

Order count by sell date: = 
CALCULATE (
    COUNTROWS ( order_fact ),
    USERELATIONSHIP ( 'Calendar'[Date], order_fact[sell_date] )
)

However, if counting of the progress is needed, slightly different approach has to be considered.

Order count in progress: = 
CALCULATE (
    COUNTROWS ( order_fact ),
    REMOVEFILTERS ( 'Calendar' ),
    order_fact[purchase_date] <= MAX ( 'Calendar'[Date] ),
    order_fact[sell_date] >= MIN ( 'Calendar'[Date] )
)