Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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] ) ) )

amitchandak
Super User
Super User

@Anonymous , 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) )

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors