SOLVED

Automated Resource forecast

Brass Contributor

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

Castellum812_0-1650960076078.png

 

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

@ASthinking something like this.

 

But get this error.

 

Expression.Error: We cannot apply field access to the type DateTime.
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)

 

 

 

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

@Riny_van_Eekelen 

Something like this

Project numberHoursStart dateEnd date
Project12921-2-202030-12-2022
Project24104-1-202126-8-2022
Project3361-1-202130-12-2022
Project43361-1-202130-9-2022

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

@Riny_van_Eekelen 

 

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.

ProjectnumberHoursStart dateEnd dateMonth EomonthDays leftHours / period
Project12921-2-202030-12-2022April 30-4-202220,2
Project12921-2-202030-12-2022Mei 31-5-2022313,7
Project12921-2-202030-12-2022Juni 30-6-2022303,5
Project12921-2-202030-12-2022Juli 31-7-2022313,7
Project12921-2-202030-12-2022Augustus 31-8-2022313,7
Project12921-2-202030-12-2022September30-9-2022303,5
Project12921-2-202030-12-2022Oktober 31-10-2022313,7
Project12921-2-202030-12-2022November 30-11-2022303,5
Project12921-2-202030-12-2022December30-12-2022303,5
         
Project24104-1-202126-8-2022April 30-4-202223,3
Project24104-1-202126-8-2022Mei 31-5-20223151,7
Project24104-1-202126-8-2022Juni 30-6-20223050
Project24104-1-202126-8-2022Juli 31-7-20223151,7
Project24104-1-202126-8-2022Augustus 31-8-20223151,7

@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.

 

@Riny_van_Eekelen 

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.

 

 

 

 

 

 

 

@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 (Brass Contributor)
Solution

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

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

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

1 best response

Accepted Solutions
best response confirmed by Castellum812 (Brass Contributor)
Solution

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

View solution in original post