Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II
Helper II

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

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. 


09/01/21 BShipping2
03/15/23 DBought4
04/04/22 EBought5


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!

Super User
Super User

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 =
    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.

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



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.

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?

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.


You can use the following DAX query - 


Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.