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.
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!
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.
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.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.