Re: Calculating Hold Times Over Time - ChatGPT Gav... - Microsoft ...
Hi, I am facing a challenge in Power BI Desktop with calculating hold times over time between two date fields. The table in question, CODI, contains [Hold Start Date] and [Hold End Date], and I need to determine both the count of people on hold and the average days on hold between these dates.
Several complexities arise in this process:
- Records with a null [Hold Start Date] should be ignored.
- For records with a [Hold Start Date] but a null [Hold End Date], I aim to use the last day of the preceding month for calculations.
- I intend to present counts and averages based on Fiscal Year, Fiscal Quarter, and Fiscal Month. To support this, I have a date table, _DatesOnHold, with relevant date attributes.
I have visualized the data in Excel and produced the following results:
I attempted to use ChatGPT for a solution, but I didn't achieve the desired outcome. Therefore, any assistance on this matter would be greatly appreciated!
Unfortunately, I cannot provide my pbix file due to containing sensitive health information, but I will anonymize it before sharing. Also, wishing everyone a happy Thanksgiving Weekend (I'm based in Canada)!
If you can offer sample data with your solution in a usable format, it would be immensely helpful. Please exclude any sensitive or irrelevant information.
For assistance on uploading data, refer to this guide.
To expedite responses, check out this resource on quickly getting your questions resolved.
Stay updated with the latest features introduced in the September 2024 Power BI update and engage with the Fabric Community for valuable insights and opportunities!
