Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a need to report on the number of working days between two events that will happen to a person. The dates of these events will be recorded in the same column of a table each time (creating a new row for each event per person). I need to know if the latest event happened to a person within 14 working days of the previous event. I then need to be able to summarise all of the latest events and show in a visualisation the number of people that had events happen within 14 working days of the previous one, compared to the number of people whose previous event happened more than 14 days prior to the last one.
I have managed to achieve this modelling while using the Wide World Importers DW database using the Fact.Orders and Dimension.Customer tables as surrogates. My steps were:
All of this gives me the output that I need, but it has a lot of steps and I am wondering if anyone can think of ways to improve performance and efficiency, or combine steps?
Edit - this is the output I have achieved - shows the customer name, last two most recent order dates and the working days between them, as well as a donut chart showing proportion in asl v outside asl:
I used the WideWorldImportersDW database - easily available as a sample from Microsoft.
HI @Keith_Fisher,
According to your description, it seems like a common date range analysis requirement based on multiple date fields. If that is the case, I'd like to suggest you take a look at the following link 'start date', 'end date' part:
Regards,
Xiaoxin Sheng
@Keith_Fisher yes use dropbox/onedrive/google drive
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Keith_Fisher It will be much easier to understand if you put sample data in an excel file and expected output. You have provided a great explanation but hard to follow without looking at the expected output.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
thanks for the advice, this is my first post on the board 🙂
maybe I'm being dense but how can I attach a spreadsheet - I've embedded the screenshot of my output but can't put an xlsx in in the same way, and there seems to be no 'attachment' option here. Do I need to put the spreadsheet in dropbox and embed a public link or something? I must be missing smoething...
Keith
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |