Calculating Hold Times Over Time - ChatGPT Gave Up!

[fussimist]

Hi, I desperately need help for a problem that ChatGPT can't solve... I have a table called CODI in Power BI Desktop (Sept 2024)_x64_2.136.1202.0) with two Date fields CODI[Hold Start Date] and CODI[Hold End Date]. I want to calculate two things:

1) the number of people / rows on hold between the two dates; and,

2) the average number of days on hold between the two dates.

The problems arise because:

1. Many records have a null CODI[Hold Start Date] and those records should be ignored;

2. Many of the records that have a CODI[Hold Start Date] have a null CODI[Hold End Date], so I want to use the last day of the last complete month for the calculations for those records.

3. I want to report the counts and averages by Fiscal Year, Fiscal Quarter, and Fiscal Month. I have a date table _DatesOnHold with [Date End of Month], [Date Start of Month], [HoldEnd FY/FY], [HoldEnd FYMth], and a Hierarchy built on these last three. CODI[Hold Start Date] = _DatesOnHold[Date] in a Many to One active relationship.

This is the relevant data from the CODI table:

I calculated the data in Excel, and this was the result:

This is what it should look like in Power BI for the Count of Cases on Hold, and the Average Days on Hold:

I tried to use ChatGPT to solve this - wasted way, way too much time with it. I would greatly appreciate any help you can give!

Thanks, Lee

[DataNinja777 · Accepted solution]

Hi @fussimist ,

I attempted to reproduce your desired output and noticed what appear to be manual calculation errors in several cells of the Excel screenshot you provided.

I assume your requirement is to calculate the number of days between the hold start and hold end dates, while respecting the month filters.

This can be achieved by using the following DAX formulas:

Number of days held per month = 
SUMX(
    'Table',
    VAR HoldStart = 'Table'[Hold Start Date]  // Start of the hold period
    VAR HoldEnd = IF(ISBLANK('Table'[Hold End Date]), TODAY(), 'Table'[Hold End Date])  // End of the hold period (or today if no end date)
    VAR CalendarStart = STARTOFMONTH('Calendar'[Date])  // Start of the current month
    VAR CalendarEnd = ENDOFMONTH('Calendar'[Date])  // End of the current month
    VAR ActualStart = MAX(HoldStart, CalendarStart)  // Actual start, either the hold start or the start of the month, whichever is later
    VAR ActualEnd = MIN(HoldEnd, CalendarEnd)  // Actual end, either the hold end or the end of the month, whichever is earlier
    RETURN
    IF (
        HoldStart <= CalendarEnd && HoldEnd >= CalendarStart,  // If the hold period overlaps with the current month
        DATEDIFF(ActualStart, ActualEnd, DAY) + 1,  // Calculate the number of overlapping days
        BLANK()  // Otherwise, return blank
    )
)

Then this can be averagexed in the following manner:

Average days = averagex('Table',[Number of days held per month])

The dax formula above is visualized as shown below:

Your second requirement, to display the count of cases on hold, can be achieved using the following DAX formula:

Count of cases on hold = calculate(count('Table'[Hold dates]),filter('Table',[Average days]<>blank()))

This is visualized as shown below:

I have attached an example pbix file.

Best regards,

[fussimist]

Thank you so so so so much. You have no idea how much anguish you have saved me. You really are a Data Ninja! Lee

[fussimist]

Sorry, I gave what I could at the time. My pbix file contains personal health information and is not on a cloud format, just the desktop version, so I will have to anonamize it before uploading it. I will do that after the holiday (I am in Canada and it is now Thanksgiving Weekend here - Happy Turkey Day!). Cheers, Lee

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...