March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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!
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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
84 | |
82 | |
74 | |
49 |
User | Count |
---|---|
160 | |
144 | |
103 | |
74 | |
57 |