S Curve Dvelopment - Forecast Line

[patrickjfraser]

Hello,

I am creating a Progress S Curve plotting a baseline, actual and forecast values. I have the running cumulative lines working fine with one exception. My forecast line does not meet my actual line on the graph.

The reason it does not meet is that the forecast table starts on 6th May and I have actual data through to the 11th May. My measure for the cumulative goes as such:

Cumulative Forecast = if(ISBLANK(SUM(P6_Overall_Latest[Hours Planned (Forecast)])),BLANK(),calculate(sum('D&C_MilestoneProgresswPersonnel'[Installed Hours])+sum(P6_Overall_Latest[Hours Planned (Forecast)]),filter(all(M_Register_Dates[Date]),isonorafter(M_Register_Dates[Date],max(M_Register_Dates[Date]),desc))))

At the moment i cannot understand how to it using the a variable formula that gives me a consistent value and subtracts from every entry.

I tried the below but it only subtracts it from the time period that it is in. I need it to subtract off every entry:

Cumulative Forecast = if(ISBLANK(SUM(P6_Overall_Latest[Hours Planned (Forecast)])),BLANK(),calculate(sum('D&C_MilestoneProgresswPersonnel'[Installed Hours])+sum(P6_Overall_Latest[Hours Planned (Forecast)]),filter(all(M_Register_Dates[Date]),isonorafter(M_Register_Dates[Date],max(M_Register_Dates[Date]),desc)))-calculate(sum(P6_Overall_Latest[Hours Planned (Forecast)]),datesbetween(M_Register_Dates[Date],min(P6_Overall_Latest[Date]),today())))

I believe i am on the right track as if i swap the calculation for a constant value (the delta between the 2) i get the lines to meet perfectly:

Cumulative Forecast = if(ISBLANK(SUM(P6_Overall_Latest[Hours Planned (Forecast)])),BLANK(),calculate(sum('D&C_MilestoneProgresswPersonnel'[Installed Hours])+sum(P6_Overall_Latest[Hours Planned (Forecast)]),filter(all(M_Register_Dates[Date]),isonorafter(M_Register_Dates[Date],max(M_Register_Dates[Date]),desc)))-13659)

Unfortunately this value would only be suited to one day so i want to fix the calculation.

Any assistance would be greatly appreciated

[Netrelemo]

What type of visualisation are you using? I have been trying all day to make a S-Curve, and concluded it just cannot be done. 

[Anonymous]

@patrickjfraser -

Please see this Quick Measure tutorial. 

The basic idea is to determine the latest relevant date for Actuals and use that date to calculate forecast. In your case, you can substitute the Cumulative calculation for the YTD calculations.

Hope this helps,

Nathan

[patrickjfraser]

@Anonymous I will have to try it out. The only thing i noticed at a quick glance is that it uses max date from the sales (in my case actuals). My data is live linked so continuously has more data but i worked around it by making the values 0 if they are above the first date in the forecast and then added the forecast values to them. For most weeks that is 0 but it enables my final weeks to close the gap. and overlap the lines with the forecast line in front

Will definitely give your method a shot when i get a chance though. Looks a lot cleaner

[v-xicai]

Hi @patrickjfraser ,

I am not sure what desired result would you want, could you please share your sample data or desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

For example: could you please make a description for the number below?

Best Regards,

Amy

[patrickjfraser]

Hello @v-xicai 

The matching line is what i am trying to get. 

I have 3 values - Baseline value, actual value and forecast value

In the example the actual values run until 12th May. The forecast values start on 6th May.

As per the measure the forecast is a cumulative forecast line + actual to date so that they meet. The curve is correct but unfortunately as there are dates where the 2 overlap (6-12th May) it is forcing a misalignment between the 2 lines. 

The 13659 is the delta between the 2. If i subtract that as a constant value (number) it fixes the alignment issue with the line however, when i use a formula to rectify as per the middle example it only rectifies it for the current week. I need to do that calculation in such a way that it then subtracts for every possible date that the forecast line runs for