Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have two tables. A fact table of "buying date", "Selling date" and fruit list. The second table is the date table. https://app.powerbi.com/groups/me/workbooks/163ca3f1-4bdd-4c1c-a43c-0ef27fa1e29c
I would like to display in a table the number of fruits bought and sold for a given day, week or month.
The formula for calculate and userlationship is much appreciated.
Best
QS
Solved! Go to Solution.
Dale,
Thanks so much for the visual explanation and DAX formula. It's going to solve many issues for me.
Best
Qmars
Hi @qs,
We can't open the link. You can upload the sample to the cloud drive like OneDrive, Dropbox. Then share the link here. Seems your needs are simple. You can drag the day, week, month from the date table, then use the built-in aggregation.
Best Regards,
Dale
Dale,
It would be great to have DAX formula.
Best
Q
Hi @qs,
1. Create two valid date columns because the original date have time parts.
ValidBuyingDate = DATEVALUE([BuyingDate])
ValidSellingDate = DATEVALUE([SellingDate])
2. Create two relationships.
3. Create two measures.
bought = COUNT(Table1[Fruit])
Sold = CALCULATE ( COUNT ( Table1[Fruit] ), USERELATIONSHIP ( 'Calendar'[Date], Table1[ValidSellingDate] ) )
You can check out the demo in the attachment.
Best Regards,
Dale
Awesomeness ...
Dale,
Thanks so much for the visual explanation and DAX formula. It's going to solve many issues for me.
Best
Qmars
Hi Dale,
Please find below a link to the excel file:
https://www.dropbox.com/s/3dlko7sgmw2vukf/Fruit%20Basket.xlsx?dl=0
Look forward to hearing from you.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |