cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sbowman
Frequent Visitor

Issue using a Slicer to select value that adds to Start Date to generate Estimated Completion Dates

I have a table of project data that contains and a Start Date. I created a Table that generates whole numbers from 1 to 84 in increments of 1. This is then used in a Slicer to allow users to select the estimated duration in months. This value is then used to add to the project Start Dates to calculate an Estimated Completion Date. This would be used in Gant Charts or to possibly generate normalized distribution curves for estimated labor. However, I am having issues with getting this to work correctly.

 

The Table is generated with:   Estimated Duration = GENERATESERIES (0, 84, 1)

Then this measure is created:  Duration Months = SELECTEDVALUE( 'Estimated Duration'[Months], 18)

 

To add this selected Value to the Start Date I tried the following measures:

 

First:  

Estimated End Date = CALCULATE( SUMX( ProjectDates , EDATE(ProjectDates [Start_Date].[Date] , 'Duration'[Duration Months] ) ) )

 

Secondly:  

Estimated End Date = CALCULATE( SUMX( ProjectDates , DATEADD( ProjectDates [Start_Date].[Date] , 'Estimated Duration'[Duration Months] , MONTH ) ) )

 

Both result in the same error code. I also tried replacing the measure in the EDATE with just a 12 and received the same error.

 

"Invalid OADate value '32880611'. Accepted values are between -657435.0 and 2958465.99999999"  

 

Dates are in the proper format, and I removed all blanks and nulls from the date column.

 

Any Suggestions?

 

1 ACCEPTED SOLUTION
sbowman
Frequent Visitor

I was able to get this working with my initial DAX code AFTER I switched out my generated Table and Measure to using the "What If" Parameter to generate the table and slicer instead. Then using that measure inside the DAX code below got it working correctly to add the number of months selected to each individual Start Date for Gant Charts and Tables.

 

Estimated End Date = CALCULATE( SUMX( ProjectDates , EDATE(ProjectDates [Start_Date].[Date] , 'Duration'[Duration Months] ) ) )

View solution in original post

3 REPLIES 3
sbowman
Frequent Visitor

I was able to get this working with my initial DAX code AFTER I switched out my generated Table and Measure to using the "What If" Parameter to generate the table and slicer instead. Then using that measure inside the DAX code below got it working correctly to add the number of months selected to each individual Start Date for Gant Charts and Tables.

 

Estimated End Date = CALCULATE( SUMX( ProjectDates , EDATE(ProjectDates [Start_Date].[Date] , 'Duration'[Duration Months] ) ) )

View solution in original post

amitchandak
Super User IV
Super User IV

@sbowman , You can create a measure like

 

Estimated End Date =
var _dt = max(ProjectDates [Start_Date])
return
date(year(_dt) , month(_dt) + [Duration Months] , day(_dt) )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thanks for the recommendation @amitchandak 

I tried that DAX expression, and dropped it on the table, but it only returns the max estimated end date (across all projects on the table), not the date for each project on the table, or Gant Chart.

 

Any other suggestions?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors