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

S Curve Dvelopment - Forecast Line

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.Capture2.PNG

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())))Capture3.PNG

 

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)Capture4.PNG

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

 

Any assistance would be greatly appreciated

 
4 REPLIES 4
Anonymous
Not applicable

@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

@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

Community Support
Community Support

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?

 

 

6.png

 

Best Regards,

Amy

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

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors