Apr 26 2022 01:09 AM
Apr 26 2022 01:09 AM
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
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.
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 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|
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.
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 08:03 AM