Help with time intelligence and calculations

[artfulmunkeey]

Hi all,

back again for some much needed help! I am strugglign with the following problem and wonder if anyone can advise?

I have the following tables; Total remainng Hours by month and Actual Hours booked by month, linked by common project code columns.

MonthYear	Remaining Hours
Jan-20	4850
Feb-20	4650
Mar-20	4550
Apr-20	4250
May-20	3900
Jun-20	3500
Jul-20	3300
Aug-20	3150
Sep-20	3150
Oct-20	3150
Nov-20	3150
Dec-20	3150

MonthYear	Actual Hours
Jan-20	150
Feb-20	200
Mar-20	100
Apr-20	300
May-20	350
Jun-20	400
Jul-20	200
Aug-20	150
Sep-20
Oct-20
Nov-20
Dec-20

I want to predict the remaining hours by using the actual YTD and then timephasing the remaing hours by the remaining days/weeks in the year. I already have the total planned hours reducing by the actual hours monthly but cannot get my head around how to merge the two without duplicating.

Something similar to the below.

MonthYear	Actual&Remaining
Jan-20	150
Feb-20	200
Mar-20	100
Apr-20	300
May-20	350
Jun-20	400
Jul-20	200
Aug-20	150
Sep-20	787.5
Oct-20	787.5
Nov-20	787.5
Dec-20	787.5

I suspect I need a separate table which calculates the remaining days/weeks, and some way of filtering the remainnig hours depending on current date, however I am completely stuck!

Also, would it be possible to raise a flag if the number of actual hours exceeds the planned hours, if so, how could I do this?

Any help would be greatfully appreciated!

Thanks in advance

[v-rzhou-msft]

Hi @artfulmunkeey 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[v-rzhou-msft]

Hi @artfulmunkeey 

Could you provide me with your data model or your pbix file?

I think you have delete your file which shared in your link.

Best Regards,

Rico Zhou

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

[PaulDBrown]

@artfulmunkeey 

What is the actual calculation for Sep 2020 onwards? (I take it the final table will include actual hours upto a certain date or month & the calculation for the remaining dates or months).

Basically, how do you calculate the "787.5" we see as from Sep 2020?

Also, I take it that the cutoff between actual hours and calculation is if the month has actual data in it right? Does the value count? in other words, is the date of the month relevant? (ie. if there is an actual value for Sep2020, but only for upto the 7th September, which number must be shown? the Actual value or the "calculated value"?

[artfulmunkeey]

Thanks for the fast response!

Hmm, you raise some good points and have me thinking.... I want to report actual hours YTD.

From the current date onwards I want to use the remaining hours (planned hours - actuals hours). But to timephase those remaining hours by the number of remaining days in the year. (My calculation of 787.5 is now irrelevant - i think i just used total hours divided by remaining months).

But, if the actual hours equal or exceed the remaining hours then I want to stop the calculation and flag that the hours have all been consumed.

The actual data looks more like the following, but I've applied some measures to sort and simplify it for now whilst I experiment.

Actual Hours

Trans Date	Project Code	Resource Code	Actual Hours
01/01/2020	12345	9999	5
...	...	...	...

Planned Hours

Time by Day	Project Code	Resource Code	Planned Hours
01/01	12345	9999	8.5
...	...	...	...

In answer to your question; I'd ideally like to be able to have the calculation based on current month (use actuals up to last day of previous month and use planned from first day of current month, ignoring any actual hours in current month).

Thanks!

[PaulDBrown]

@artfulmunkeey 

Can you provide a sample/dummy dataset to play around with?

Also, how do you calculate remaining dates? is it "working days" (exclude weekends and festivities)? If so, you will need to flag these in your date table

[artfulmunkeey]

Gladly... how do I do so? I have an excel with the two test data sets, would this do? I can't seem to upload it here.

[PaulDBrown]

@artfulmunkeey 

Best option is to upload to a cloud service (Onedrive, Google Drive, iCloud, Dropbox...) and share from there..

[artfulmunkeey]

Thanks... the file should be available here https://drive.google.com/file/d/1SwIy0sPVGAxkIrXT54j6Ff3RAeRzbe2O/view?usp=sharing

[PaulDBrown]

@artfulmunkeey 

Can you try again? the link you provided leads to no files