Applying a Percentage Profile to Budgeted Hours

[JohnThomas]

Hi everyone.  I have a bit of an odd request... I have already worked out a solution!  The question is: Is there a better way?  My solution is taking too much time to calculate.  I made a sample workbook that presents the solution I worked out.  If there are any "Super PQ Authors" out there I would appreaceate a review and suggestions for how to improve the modle.

The situation in a nutshell:

1. We get a feed from a SQL database, which we can't edit.  It sends us a work opportunity with a start and finish date.
2. I then assign a team to work on the opportunity.  This is usally a manager, some estimators, and a scheduler.  There can be anywhere from 1 to 5 people on the team.  Each team member is given a budged hours.
3. Each team member will work on the opportunity with a different level of effort over the the duration of the event (Between start and finish date).  For exampe, the manager will be an even spread of hours as he/she just attends weekly meetings, and helps the team, etc.  The Estimator's work is all up front.  (Front loaded) and they tend to back off once the estimate is close to finishing.  The Scheduler dose a little work up front, not much in the middle and most of the work in the tail end of the job.
4. The goal is to create a "resource profile" that allows us to budget the work more closely to how they will actually spend the hours.

Link to workbook:

https://1drv.ms/x/s!BPxG8sbQyKTUl1xi1a5NCUvPQSuV?e=lPrZa2

Thanks for any assistance!

John

[v-juanli-msft]

Hi @JohnThomas 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know.

Best Regards

Maggie

[Greg_Deckler]

Of the Super PQ Users, @ImkeF is the superest I know!

[JohnThomas]

Greg - I got your book!  Great content - I'm learning so much!  (You need a different editor however... several typos.  Nothing that takes away from the DAX code or the solutions however.)

Cheers!

[ImkeF · Accepted solution]

Hi John,

hats off for this excellent workbook with sample data.

There are a couple of things you can do, which should all find on my Performance page here: https://www.thebiccountant.com/speedperformance-aspects/ 

1. Disabling background refresh
2. Adding keys to your lookup tables (those you use in Table.NestedJoin)

What's not included in that list is a new way to speed up nested if-then-elses (I've implemented that in step "AddField_StepStartDate", but you might want to adjust other steps as well):

            Record.FieldOrDefault(
                [   1 = [Start Date],
                    2 = Date.AddDays([Step1 Finish],1),
                    3 = Date.AddDays([Step2 Finish],1)
                    ],
                Text.From([Step]),
                null)

This is a variation of the CASE-technique I've used here: https://www.thebiccountant.com/2017/12/11/date-datesbetween-retrieve-dates-between-2-dates-power-bi-... 

It makes sure that the expression is evaluated lazily: So only one of the possible cases will be evaluated.

In addition to that, it could be that my Date.Networkdays-function is faster than yours: https://www.thebiccountant.com/2020/02/20/date-networkdays-function-for-power-query-and-power-bi/  (but I haven't tested it)

Attaching the file with some adjustments.

[JohnThomas]

Imke,

Thanks so much for taking the time to look at this for me and my appologies for taking so long to get back to you.  For some reason email notifications for this thread were not getting to me!

Anyway, I am reading all the links you provided and going through the updated workbook. 

Thanks again!