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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
jarwest
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. 

 

BoughtDateSoldDateNameStatusID
10/12/2010/14/20ASold1
09/01/21 BShipping2
06/24/1907/12/22CProcessing3
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!

6 REPLIES 6
rajulshah
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 =
CALCULATE (
    COUNT ( Stock[ID] ),
    NOT ( ISBLANK ( Stock[SoldDate] ) ),
    USERELATIONSHIP ( Dates[Date], Stock[SoldDate] ),
    CROSSFILTER ( Dates[Date], Stock[BoughtDate], NONE )
) + 0

 

See the outputs below:

rajulshah_0-1682487561298.png

rajulshah_2-1682487594114.png

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?

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

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.

@jarwest 

You can use the following DAX query - 

Dates = CALENDARAUTO()

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.