cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
patrickjfraser Frequent Visitor
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
Community Support Team
Community Support Team

Re: S Curve Dvelopment - Forecast Line

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

patrickjfraser Frequent Visitor
Frequent Visitor

Re: S Curve Dvelopment - Forecast Line

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

Super User
Super User

Re: S Curve Dvelopment - Forecast Line

@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 Frequent Visitor
Frequent Visitor

Re: S Curve Dvelopment - Forecast Line

@natelpeterson 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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 56 members 1,094 guests
Please welcome our newest community members: