how to divide a weekly sum time evenly into each weekday, then compare between actual and planned

[Anonymous]

Hello,

I plan to compare actual time and planned time in line chart.

For now, the model works well with our actual working time, which is updated daily.

But we want a planned time added in, to show the difference between cumulative actual time spent and planned time.

The problem is, we plan time weekly, not daily. So the query data only record our planned time on the last day of our week. So for now, our planned cumulative time only shows a dot on the last workday, rather than demestrate as a line.

(P.S. our company's last workday is Thursday, rather than Friday, I have convert the natual week and day into our company's work calendar)

Could anybody help us to divide our weekly planned time evenly into daily, then cumulate them into weekly, to compare with daily cumulative actual time?

shared file is here:

Actual VS Est

Many tks~

[Ashish_Mathur · Accepted solution]

Hi,

See if my solution here helps.

[Ashish_Mathur]

Hi,

That link requires log-in credentials.

[Anonymous]

don't see proper choice to share this file...

[Ashish_Mathur]

Hi,

Upload the files to Google Drive and share the download link.

[Anonymous]

https://drive.google.com/open?id=1w7uXEcw2Fta0t3BsOpfBvQ6w6AKLA7zA

[Ashish_Mathur]

Hi,

I would need to carry out a transformation in the "asBUILT Team Job Planning 2019.xlsx" file.  Share the link from where i can download this file.

[Anonymous]

Data_Weekly_TeamJobPlanning

[Ashish_Mathur]

Hi,

There is a prolem in the data on that tab.  For a few rows, the date appearing in the Period Beginning date column is greater than the date appearing in the Period Ending date column.

Please correct for this.

[Anonymous]

Hello Ashish, 

thanks for pointing the error, and we are trying to update our database then re-export data.

Meanwhile, is there any function can go chenck dates between "Period Beginning" and "Period Ending", then delete the wrong rows

I know "Value.Compare" can return/compare which is greater between two columns, giving me -1 when beginning less then ending date, then I can delete the rows with -1, to get a cleaned data

Is there any way to those two steps in one step, to merge the steps? 

[Ashish_Mathur]

Hi,

In the Query Editor, you cannot delete any rows - you can only filter them out.  It is ideal to correct for this error in the database itself.

[Anonymous]

Daily Actual VS Est 20190207 

Team Job planning 2019 (the data source file recording the planned time)

Many tks,

[Ashish_Mathur · Accepted solution]

Hi,

See if my solution here helps.

[Anonymous]

Thanks Ashish,
learned a lot from how you extract numbers/days from the week, and evenly divide the planned time into each day. Very impressive.

For now, I still have two unsolved problems:

Question 1
I added a column to show sum time of [SCP Res.] and [BIM Res.]
But seems the function doesn't work fine, and I couldn't find out why.
And I think that is the reason why the current pivot chart show so huge time difference between [Cumulative Time] and [Cumulative Daily Planned Time]
Please HELP.

Question 2
The current query table has 77 errors, but I could not make the errors showed up and correct them. Would you please help?

[Anonymous · Accepted solution]

File is here

[Ashish_Mathur]

Hi,

I cannot see any transofrmation because i do not have the source data files.

[Anonymous]

Morning Ashish,

This is the data source of planned time,
Planned Time (data source)

 
Table Name: Data_Weekly_TeamJobPlanning
Relevant Columns are: SCP Res. and BIM Res.

I tried to add a new column to get a total/sum "Planned Res."
But the function doesn't work fine.

And the above shared data source is imported into the Pivot Table below via Query Editor

Est VS Actual

Thanks for have a look.

[Ashish_Mathur]

Hi,

You get errors in the Data_Weekly_TeamJobPlanning Table because there are N/A and NA entries lying in numeric columns of your base Excel table.  Using Find and Replace, remove both those text entreis from your worksheet, close that file and then refresh.  Those errors should vanish - tried and tested.

As regards your first problem, try to write a measure instead of an M formula.  If you need my help with writing the measure, tell me what exact result you are expecting (in a simple Table format).

[Anonymous]

But the measurement for adding SCP Res and BIM Res together has not been solved.

What I plan to do is to get a column "Planned Time" to record the sum of SCP Res and BIM Res. Under Query Editor, now the fomula is like this:

= Table.AddColumn(#"Removed Errors", "Planned Time", each [#"SCP Res."]+[#"BIM Res."])

But the problem is, after I delete/remove all the NA and N/A errors, still a lot data under SCP Res and BIM Res show "null", and "null" add anything still shows "null', so my "Planned Time" now doesn't show sum of SCP Res and BIM Res 

I tried  "Transform / Replace Values", to use "0" replace all "null" .

And it works, all Planned Time are Numeric Whole numbers, and the total/sum looks fine:

But after I replace value, the data cannot be loaded into Excel, showing Error like this:

My thinking is the planned time can sum of "SCP Res" and "BIM Res" under Query Editor, row by row .

Then follow the previous formula you taught me, to break down the planned time evenly into days,

Then cumulate them into weekly (or any periodical term)

Fianlly to compare Planned time with Actual used time.

The Actual used time is recorded daily, and can cumulate into weekly data

But, the Planned time is planned seperately under "SCP Res.' and "BIM Resx", weekly (not daily)

I need to first get a sum weekly "Planned time",

then break down to daily planned time,

then cumulate the daily planned time

If you know any measurement can do better clean up or calculation then what I did under Query Editor, please let me know.

Thank you very much for the help

[Anonymous]

Hi Ashish,
I didn't correct the NA and N/A error in raw data table (Data_Weekly_TeamJobPlanning Table), but to remove those errors under Query Editor after being imported into Query, 70 errors been deleted, and left 7028 rows can be successfully loaded.

When you say "Using Find and Replace, remove both those text entreis from your worksheet, close that file and then refresh.", you mean I should do it under raw file before it's been imported into Query or do it under Query Editor? Is there difference between?

When I tried to correct error under raw data table before imported into Query. But when I tried Find and Remove "NA" and "N/A" from "prog.Est", "RCP Res." and "BIM Res." columns, the error number doesn't match 70 errors found under Query Editor.

So I just remove error under Query Editor.

Currently, I did under Query, select "prog.Est" column, and "Remove Rows / Remove Errors", then 70 rows been removed, and the rest 7028 rows been uploaded successully.

[Ashish_Mathur]

Hi,

Find and Replace has to be used in the Query Editor.