Help predicting

[KOLD1313]

Hello ,

I am new using powerbi. I was wonder is I can make estimate of the best scenerio on dates. I have data for start date and end date of investigation. Some end dates are in blank because the investigations are still in process. Can powerbi create a estimate to shown  the best scenerio of date for clousure of this investigation to mantain a monthly average below 45 days between the start and clousure of the investigation but close to 35 days.

[saud968 · Accepted solution]

Calculate Duration:
Create a new column to calculate the duration between Start Date and End Date. You can use DAX (Data Analysis Expressions) for this:
Duration = DATEDIFF([Start Date], IF(ISBLANK([End Date]), TODAY(), [End Date]), DAY)

Estimate Closure Dates:
For ongoing investigations, estimate the closure date to maintain an average duration close to 35 days but below 45 days. You can create a new column for estimated closure dates:
Estimated Closure Date = IF(ISBLANK([End Date]), [Start Date] + 35, [End Date])

Calculate Monthly Average Duration:
Create a measure to calculate the average duration per month:
Average Duration = AVERAGEX(VALUES('Table'[Month]), [Duration])

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[Anonymous · Accepted solution]

Hi @KOLD1313 ,

Nice insights from saud968.

In addition, you can use the numeric range parameter to simulate different closure dates for ongoing investigations:

• Go to the Modeling tab and select "New Parameter".
• Set the parameter range (e.g., 30 to 60 days) and increment (e.g., 1 day).

Create a new measure to estimate closure dates for ongoing investigations:

Estimated Closure Date = IF(ISBLANK(MAX('Table'[end date])), MAX('Table'[start date])+[Days Value] , MAX('Table'[end date]))

As shown in the results below, you can dynamically adjust the number of days to get different results.

Reference:

Use parameters to visualize variables - Power BI | Microsoft Learn

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[saud968 · Accepted solution]

Calculate Duration:
Create a new column to calculate the duration between Start Date and End Date. You can use DAX (Data Analysis Expressions) for this:
Duration = DATEDIFF([Start Date], IF(ISBLANK([End Date]), TODAY(), [End Date]), DAY)

Estimate Closure Dates:
For ongoing investigations, estimate the closure date to maintain an average duration close to 35 days but below 45 days. You can create a new column for estimated closure dates:
Estimated Closure Date = IF(ISBLANK([End Date]), [Start Date] + 35, [End Date])

Calculate Monthly Average Duration:
Create a measure to calculate the average duration per month:
Average Duration = AVERAGEX(VALUES('Table'[Month]), [Duration])

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!