Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I'm trying to create a measure that will calculate the number of days or weeks between Sales for a certain customer.
I'm struggling to get a working measure so any help would be greatly appreciated.
I've the link to sample Power BI data attached here too for your reference.
Many Thanks in advance!
Power BI File - https://drive.google.com/file/d/19ATFPApHUaFITZ9Yg1XmRxhbCS2uxifV/view?usp=sharing
Excel link - https://docs.google.com/spreadsheets/d/1kjrOwCWgMqvXCaDTpyFc8iJavVb4jtRR/edit?usp=drive_link&ouid=11...
First step would be to check and adjust your data model.
Market Table is not a dimension
Item Table and Customer Table need single direction
What's the purpose of the Period table?
Oh yes, I've updated this now thank you!
The period table was just a table created to put in the Financial Year etc but it's not being used here now.
The updated PBIX is here now - https://drive.google.com/file/d/19ATFPApHUaFITZ9Yg1XmRxhbCS2uxifV/view?usp=sharing
Hey, is there any solution you have been able to see from my data by any chance?
I've tried a few DAX measures but to no luck...
Why is Canada twice in your Market table?
Apologies, that was put in twice in error. I can remove it now and let you know once removed.
This has been updated now - https://drive.google.com/file/d/1ZOgSn00w6ZTbccryhUdEq6DhwN13UtRn/view?usp=drive_link
Here's a more appropriate version of the data model
But you can also opt not to connect the Calendar table.
Now your question is
calculate the number of days or weeks between Sales for a certain customer
That would require multiple transactions per customer. Your sample data only lists each customer with a single transaction.
Please use the attached to improve your sample data.
In case of more than two transactions per customer do you want to see the individual differences, an average, or something else?
I've followed your tips now and the differences seem a good bit off:
Some customers have more than 2 transactions as per the screenshot attached. I want to see the difference between each date for the customers. I have the customers in a slicer so only need to see the days between each transaction date.
I want to see the difference between each date for the customers.
How would that look like in a report? I cannot visualize it.
This is how it would look:
Where did the 130 come from?
I'm not sure where the 130 came from...
The other figures were correct though.
please modify the sample PBIX to include a couple of customers with multiple transactions.
I was actually in the process of doing that to try make things easier to understand:
PBIX - https://drive.google.com/file/d/1MwFy4oJuBzzrmwJ7MPCSJkuMkFMU-xxl/view?usp=drive_link
Thank you.
User | Count |
---|---|
88 | |
72 | |
69 | |
65 | |
57 |
User | Count |
---|---|
96 | |
92 | |
91 | |
75 | |
69 |