Forum Discussion
Help with time intelligence and calculations
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"?
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!
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
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.
Best option is to upload to a cloud service (Onedrive, Google Drive, iCloud, Dropbox...) and share from there..