Forum Discussion
Help predicting
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.
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!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.
2 Replies
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.
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!