Assistance with Planned vs Actual Project Completion Measure

[scpono86]

Hi all,

I'm trying to calculate actual % complete vs forecast % complete. I have measures to calculate the actuals and the forecast, but I'm having trouble linking them together for a running total.

In the table, I've calculated the forecast per day and the actuals up to the previous Friday.

I've written a measure to join the two together, but it's not working as I'd like. I want it to calculate a running total so that:

• FY Week 12 = 12.67%
• FY Week 13 = 16.56% (12.67% [actual] + 3.89 [forecast])

Here's my current measure:

SALES & FORECAST =
VAR LASTACTUALDATE =
CALCULATE ( MAX ( '6 - Actual'[Date] ), ALL ( '0 - Date' ) )
VAR ACTUALS = [Cumulative Actual Completion]
VAR FUTUREFORECAST =
CALCULATE (
[Cumulative Forecast Total],
FILTER ( '0 - Date', '0 - Date'[Date] > LASTACTUALDATE )
)
VAR RESULT = ( ACTUALS + FUTUREFORECAST )
RETURN
RESULT

As you can see the last value is equal to 87.33% where I would like it to be equal to 100% 

I hope this makes sense. Thanks in advance! 

[v-yifanw-msft]

Thank @lbendlin  for your prompt reply.

Hi @scpono86 ,

From your description, it appears that you are on the right track, but are facing some challenges in getting the desired results. The problem may be in the way the running totals are calculated or aggregated, so let us refine your approach.

1. Make sure your "0 - Date" table covers the entire range you are analysing, including all actual and forecast dates. Blank or incorrect date ranges can affect calculations.
2. Check the relationship between the date table and the actual/forecast table. Incorrect or missing relationships can lead to unexpected results.
3. Break the indicator into smaller parts for debugging. For example, calculate and look at the totals for ACTUALS and FUTUREFORECAST separately to make sure they are as expected, and then combine them.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...