Dynamic Interval for Schedule Modelling (repost from Developer)

[derekalstein]

Reposting this into the desktop forum, since this wasn't really applicable to the developer forum.

Monday

Hello - first time poster. Before you get angry, I have read through the forums and not been able to find a solution that works.

Here's what I'm trying to do.

For a number of projects with defined start dates over the next 30 years, I want to create a data modeller that allows me to flex project duration to recalculate finish date and total duration over the life of the facility (total project days). There are number of various scenarios in durations that can be considered, and i'd like to use this data modeller to reshoot a gantt chart.

I started by building a parameter with a defined range (days). This parameter, and the associated measure, was meant to be used for the Duration Value to determine a new finish date for each scheduled project.

my formula...

varFinish = dateadd(Outages[Finish Date], [Duration Value], day)

where...

- [Finish Date] is a predefined project date

- [Duration Value] is the measure being generated by the parameter

This formula was used for a column in my table to assign a new finish date to each specific project. It doesn't seem to recognize the [Duration Value] as a number. But when i manually change the [Duration Value] to a simple integer (1, 5, 4005, whatever), the finish date updates by the associated days.

I'm stumped and getting angry (mainly because i could build the same functionality in excel in a matter of minutes)

The problem seems to lie in the varFinish code. The interval value (in days) is being set by Outage[Duration Value], which is linked to a parameter between 10-40. When I use this reference to the dynamic value, the new date (varFinish) does not move from the start date - ie the number seems to be recognized as 0. But if i manually change the internval reference from Outage[Duration Value] to just a random number, the formula properly moves the date by that number of defined days.

please help!

TIA!

[derekalstein]

I'm not sure if this will address what I'm trying to do. i believe the formula would work for a single selected outage start date, but it would not apply my selected (via slicer or parameter) to a group of outages as i'm trying to do. while my data set only shows 5 projects now, my full data model will be consider 100s of projects scheduled over the next 30 years.

While I'm very impressed by PowerBI, this is quite frustrating that it does not seem to have the ability to do this. As i said, I could make this functioning model in excel quite easily - it just wouldnt give me the visualization that i'm looking for.

It feels like my only option may be to create a data set that has every combination of durations that are possible. Not complicated, but certainly alot of adminstrative work to build the data source

[v-rzhou-msft]

Hi @derekalstein ,

According to your statement, I think [varFinish] is a calculated column and you want it to be dynamic by "Duration" slicer. [Duration Value] in [varFinish] column should be a measure which is filtered by "Duration" slicer.

As far as I know, Power BI doesn't support us to create a calculated column which will be dynamic filtered by slicer. Here I suggest you to create a measure instead of a calculated column. Then show measure in a visual. And I see you use Dateadd() function and time intelligence Outages[Start Date].[Date] from your screenshot. 

As far as I know, Dateadd() will return a table. .[Date] may not be a good idea due to it will make your report complex.

Try below code to create a measure.

varFinish = SELECTEDVALUE(Outages[Start Date]) + [Duration Value]

Result is as below.

Best Regards,
Rico Zhou

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