Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vikasmca05
Frequent Visitor

Schedule Accuracy for Planned vs Actual Date

Hi,

 

I do have a table with primary key as project name where in two columns are for "Planned Date" and "Actual Date". I want to calculate schedule accuracy by measuring the deviation from Planned vs Actual fro each project. These two columns also has few rows which are not wanted in the report like null, "not applicable", "tbc"..

 

How can i get the visual chart depicting the "Planned Date"/"Actual Date" and the schedule accuracy in percentage?

5 REPLIES 5
v-yuezhe-msft
Employee
Employee

@vikasmca05,

Here I filtered the invalid values directly, I made a sample as below:

1. Enter the data that we need.
2. Create a measure and set the values as percentages in Modeling.

per2 = MAX(Table2[Planned Date])/ MAX(Table2[Actual Date])


After that we can get a table visual like this:
1.PNG


For more information, please check the pbix as attached.


Hope that will be suitable, if not, could you please share sample data and post desired result to me? Thanks in advance.

 



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for the reply.

How can we caluclate only Weekday in the accuracy?

 

If the Actual date is much before the planned date then it is not providing the correct value.

Basically my table would look like -

 

Schedule Accuracy is obtained by formula -

=IF(OR(NETWORKDAYS(RPC,RTW Planned)=0,NETWORKDAYS(RPC,RTW Planned)<0),1,NETWORKDAYS(RPC,RTW Actual)/NETWORKDAYS(RPC,RTW Actual))

 

How can i draw the chart like below Untitled.png-

ReleaseRPCRTW Planned RTW ActualSchedule AccuracySchedule Accuracy
ID 1 7-Jul-1716-Oct-1716-Oct-17100%0
ID 217-Nov-1716-Aug-1716-Aug-17100%0
ID 326-Jan-1826-Mar-189-Apr-18124%

10

Hi,

Any answer please?

@vikasmca05
 
Based on my test, we can take the following steps to meet your requirement.
 
1.  Enter the data and create a time table using the function CALENDARAUTO

dimtime = CALENDARAUTO() 


2.   Then we need to create another two columns to calculate the net workdays later.

weekday = WEEKDAY(dimtime[Date])
countweekday = SWITCH(dimtime[weekday],1,0,7,0,1) 


3.   In your table, we can use the formulas as below to get the Schedule Accuracy。

 

Schedule Accuracy =IF( Table1[RTW Actual]<=Table1[RTW Planned],0,CALCULATE(SUM(dimtime[countweekday]),FILTER(ALL(dimtime),AND(dimtime[Date]>=Table1[RTW Planned],dimtime[Date]<=Table1[RTW Actual]))))
Schedule Accuracy1 = IF(Table1[Schedule Accuracy]=0,1,Table1[RTW Actual]/Table1[RTW Planned])


4.  Then we can get the result in Line and stacked column chart.

 

 1.PNG

 

In addition, the Schedule Accuracy should be 11 here. As I made a test in excel, please refer to the picture as below.

2.png
 
For more information, please check the pbix as attached.



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.