Making a filter that filters on different date times based on a different column

[jarwest]

Hey all,

I'm currently working on adding a card to a report that shows the number of items bought and sold. Items are stored in a table similar to the one below, where items will be bought first, then sold. 

BoughtDate	SoldDate	Name	Status	ID
10/12/20	10/14/20	A	Sold	1
09/01/21		B	Shipping	2
06/24/19	07/12/22	C	Processing	3
03/15/23		D	Bought	4
04/04/22		E	Bought	5

In the end, I want to be able to count the number of items that were bought and the number of items that were sold within a given period. I know I can use Status to differentiate between if it has been bought or sold, but I think I can also just as easily use if SoldDate is null to differentiate. 

I need to be able to use a slicer to filter on these values by date. So, I can set the slicer to 12/12/22 and get that only 2 records were bought or sold after 12/12/22, and both were bought, so I'd have 2 bought and 0 sold. If I set the slicer to 8/10/21, I'd get 3 bought and 1 sold.

I don't need to know anything but the final count based on using the slicer to decide how far back I want to go. If an item was both bought and sold during the time period, it should count for both values. So, if I set the slicer all the way back to 1/1/2016, before any date in this calendar, I should have 5 bought and 2 sold.

Any help is greatly appreciated, let me know if I need to clarify anything. I haven't reached out throguh this resource in a while and I might be using the wrong layout, and I do apologize if I am.

I hope you're all having a good day!

[rajulshah]

Hello @jarwest ,

I have used the same data as provided and used following DAX measures:

Bought =
CALCULATE ( COUNT ( Stock[ID] ), NOT ( ISBLANK ( Stock[BoughtDate] ) ) ) + 0

Sold =
CALCULATE (
    COUNT ( Stock[ID] ),
    NOT ( ISBLANK ( Stock[SoldDate] ) ),
    USERELATIONSHIP ( Dates[Date], Stock[SoldDate] ),
    CROSSFILTER ( Dates[Date], Stock[BoughtDate], NONE )
) + 0

See the outputs below:

Please let me know if this didn't work.

[jarwest]

Where is Dates[Date] coming from? Do I need to define a new data source? What does this dates table look like?

[rajulshah]

@jarwest 

It is a dimension date table that we always create in a power bi report to relate it to date field of another fact table. And we use the dimension date table when we use any date related DAX functions.

[jarwest]

Just to follow up, I tried creating my own date table, I don't know if I used the same method, but I'm getting the following error when I try to create your Sold measure. 

'USERELATIONSHIP function can only use the two columns references participating in relationship.'

Should I create a relationship between my data, what should it look like, which date column should I join on?
Which date time should I be filtering on here to get the expected outcome?

[jarwest]

Hey, can you link me a resource that shows how to make this date table? I can mark this as a solution, but I'd like to know how to make this new value before doing so.

[rajulshah]

@jarwest 

You can use the following DAX query - 

Dates = CALENDARAUTO()