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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors