Forum Discussion
HELP to solve this condition
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.
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] ) )
1 Reply
- Jihwan_Kim
Super User
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] ) )