Apr 26 2022 01:09 AM
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 ;)
Apr 26 2022 03:30 AM - edited Apr 26 2022 03:42 AM
@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)
Apr 26 2022 04:36 AM
@Castellum812 Would help if you could share the data, because it's difficult to come up with a solution just by guessing.
Apr 28 2022 01:56 AM
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 |
Apr 28 2022 02:27 AM
@Castellum812 Thanks, but I don't understand what you want to do with this. Perhaps someone else does.
Apr 28 2022 02:57 AM
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 |
Apr 28 2022 03:32 AM
@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.
Apr 28 2022 04:38 AM
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.
Apr 28 2022 05:50 AM - edited Apr 28 2022 05:54 AM
@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.
Apr 28 2022 05:55 AM
Solution@Castellum812 Note that I just uploaded another file to my previous post as I discovered an error.
Apr 28 2022 08:03 AM
Apr 28 2022 08:20 AM
@Castellum812 Yes, the file in my post is the correct one. Glad it worked.
Apr 28 2022 05:55 AM
Solution@Castellum812 Note that I just uploaded another file to my previous post as I discovered an error.