SOLVED

Contributor

# Automated Resource forecast

Hi There,

I have been struggling with a forecast sheet in Power Query.

I have data coming from a crm.

It contains project information including Start Date and End date of the project and the remaining hours for the project.

What I try to achieve is a pivot with a row for each project and columns for each week or month or quarter where the amount of hours is simply spread statistically form now until the enddate

For now I  setteled with a hybrid solution but I prefer solving it i na query completely.

I calculate  the amount of month from now until the enddata

I calculate the hours per month

I calculate the header for the pivot

=EOMONTH(NOW();SEQUENCE(1;MAX(Q:Q))-1) where q is the enddate column

I calculate the values for each month using the Current Date / Start date / End Date

=IF(Q25=0;;IF((U\$1:AP\$1>N25)+(U\$1:AP\$1<M25)+(U\$1:AP\$1="");;Hours_per_Period[@Hours]))

Q= remaining month

M = Start Date

N = End date

row one contains the end of month date

Output look like this

It works but it is not very dynamic.

Changes in Columns corrupt the functions to the right.

So what I would like to do is create a table containing multiple rows for each project/period

Project1, Period1, hours

Project1, Period2, hours

Project2, Period1, hours

Project2, Period2, hours

Project2, Period3, hours

And pivot this table after that.

Don't know where to start

11 Replies

# Re: Automated Resource forecast

@ASthinking something like this.

But get this error.

Details:
Value=1-5-2022 00:00:00
Key=End date

Besides that.

Not sure if the List.Generate can be expanded

``= Table.AddColumn(#"Added Custom1", "Test", each if [End date] <> null then List.Generate(() => Date.EndOfMonth(DateTime.LocalNow()), each _ < Date.EndOfMonth([End date]),each _ +1) else 0)``

# Re: Automated Resource forecast

@Castellum812 Would help if you could share the data, because it's difficult to come up with a solution just by guessing.

# Re: Automated Resource forecast

Something like this

 Project number Hours Start date End date Project1 29 21-2-2020 30-12-2022 Project2 410 4-1-2021 26-8-2022 Project3 36 1-1-2021 30-12-2022 Project4 336 1-1-2021 30-9-2022

# Re: Automated Resource forecast

@Castellum812 Thanks, but I don't understand what you want to do with this. Perhaps someone else does.

# Re: Automated Resource forecast

I would like to extend the two example rows to days or directly into weeks/month's/quarters

Output should contain something like below.

That table could be Pivited where the periods are the data columns again.

 Projectnumber Hours Start date End date Month Eomonth Days left Hours / period Project1 29 21-2-2020 30-12-2022 April 30-4-2022 2 0,2 Project1 29 21-2-2020 30-12-2022 Mei 31-5-2022 31 3,7 Project1 29 21-2-2020 30-12-2022 Juni 30-6-2022 30 3,5 Project1 29 21-2-2020 30-12-2022 Juli 31-7-2022 31 3,7 Project1 29 21-2-2020 30-12-2022 Augustus 31-8-2022 31 3,7 Project1 29 21-2-2020 30-12-2022 September 30-9-2022 30 3,5 Project1 29 21-2-2020 30-12-2022 Oktober 31-10-2022 31 3,7 Project1 29 21-2-2020 30-12-2022 November 30-11-2022 30 3,5 Project1 29 21-2-2020 30-12-2022 December 30-12-2022 30 3,5 Project2 410 4-1-2021 26-8-2022 April 30-4-2022 2 3,3 Project2 410 4-1-2021 26-8-2022 Mei 31-5-2022 31 51,7 Project2 410 4-1-2021 26-8-2022 Juni 30-6-2022 30 50 Project2 410 4-1-2021 26-8-2022 Juli 31-7-2022 31 51,7 Project2 410 4-1-2021 26-8-2022 Augustus 31-8-2022 31 51,7

# Re: Automated Resource forecast

@Castellum812 Thanks! Took you data and created a sheet, in order to try and understand your intentions. I believe I get the bigger picture now, but am confused by what I consider inconsistent data.

See attached.

# Re: Automated Resource forecast

Its what you get if in a hurry
Sorry, for that.

It's my inconsistency, End date should be the reference.

The data was incorrect because I did not calculate the remaining total day's, but copied somethin.

See attachment

I'm hoping to get some starting point after witch I ca extend the detail. For some projects start date is relevant as well, I it is in the future.

# Re: Automated Resource forecast

@Castellum812 Please find a quick-and-dirty PQ solution. Note that my numbers for Project 2 are quite different from yours, probably because you entered 5 in M17 and because I calculate the number of days left including the current date. Today is April 28, so you will have three days left in April. Today, tomorrow and the day after, being April 30.

best response confirmed by Castellum812 (Contributor)
Solution

# Re: Automated Resource forecast

@Castellum812 Note that I just uploaded another file to my previous post as I discovered an error.

# Re: Automated Resource forecast

This is exactly what I was looking for.
The file is still the file in the post above right?
Thanks for getting on track

# Re: Automated Resource forecast

@Castellum812 Yes, the file in my post is the correct one. Glad it worked.