Time to Complete Budget Allocation

Simon1 · January 13, 2021, 6:04am

Hi,

I have been trying to modify Sam’s budget allocation model. My basic scenario is that we have an estimated time in hours that we think it will take to complete a job and this time should be budgeted to be completed within a starting date and an end date. Following Sam;s model as best I can I have managed to get to the point where I can now calculated the Budgeted Job Time and The Budgeted Job Days (working days). that the time can be allocated over. I am wondering where I am going wrong with the Job Budget Time Allocation Measure as I ti only show a calculation for a single day rather than allocating this time of the number of days that the job will be done over.
My Measure looks like the following:
Job Budget Time Allocation =

VAR DayInContext = COUNTROWS(Dates)

VAR BudgetedJobDays = SUM(‘Job Budget’[Job Budget Days])

RETURN

IF(HASONEVALUE((Dates[Date])),

DIVIDE(DayInContext,BudgetedJobDays,0)*[Budgeted Job Time],0)

I have included a trimmed down version of my model that should help.
.
Thanks in Advance

DatesBetweenModel.pbix (187.4 KB)

EnterpriseDNA · January 14, 2021, 1:11am

Hi @Simon1, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

Use the forum search to discover if your query has been asked before by another member.
When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
Use the proper category that best describes your topic
Provide as much context to a question as possible.
Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

EnterpriseDNA · January 18, 2021, 1:18am

Hi @Simon1, we have a thread with some recommendations from other Power BI users which could help. Here’s the link where you can go to view it - Allocate quarterly budget and Allocation of Brand(Category) Budget using monthly seasonal fsctors . Hope this helps.

Simon1 · January 17, 2021, 11:28pm

Hi,
I have tried to re-word my post to be more specific. I am new to Power BI and using forums so I apologise if I am unclear.

I am trying to solve an issue which I think is a variation of the budget allocation models in this forum. To provide some background I have am trying to develop a measure to be able to allocate a budgeted amount of time that falls between two dates. For example I have a job that has come in and I have estimated that the job will take 9 hours to complete and this job is scheduled to be done between two dates (i.e 1/July/2020 to 25/Aug/2020) The work can only be done on workdays.

As there are a large number of jobs in our database I want to be able to use this budget allocation to build up a picture of the overall time needed to complete these jobs within the scheduled dates.

I have been working with Sam’s model and modifying it to try and get the time allocation down to a day allocation level. But the problem I have is that my measure is only providing the granularity for 1 day rather than allocation the time evenly over the work days between the 1/7/2020 to 25/8/2020.

I think I am missing a key part of the measure but I am stuck on how to write it to allocate over each day in the time period. This is the measure I have so far:

Job Budget Time Allocation = 
VAR DayInContext = COUNTROWS(Dates)
VAR BudgetedJobDays =  SUM('Job Budget'[Job Budget Days])
RETURN
IF(HASONEVALUE((Dates[Date])),
DIVIDE(DayInContext,BudgetedJobDays,0)*[Budgeted Job Time],0)

So my end goal is to be able to allocate the Total Time Budgeted over the Time Period allocated to form something like this:

I have included a cut down version of my model which I hope will help.DatesBetweenModel.pbix (150.9 KB)

Thank You.

Simon1 · January 20, 2021, 7:46am

Thank You,
I have tried to apply a lot of the examples on the forum but I am still struggling. The difficulty I am having is that I think most examples are based on a budget period such as a month or year that needs to be converted to a day granularity. I think my issue is that as I am dealing with a start date and end date I can’t get my head around how to create the measure that will allocate the time across each workday evenly and be able to sum the totals over different periods.

Melissa · January 20, 2021, 10:45am

Hi @Simon1,

This will be pretty easy if you set it up in Power Query, it’s similar to POTW #4.
Consequence will be an increase in data model size but calculating it with DAX isn’t cheap either, although there might be some optimizations possible…

Anyway this is how far I got.
Note that I had to change the relationships in the model view to inactive on both dates.

Job Allocated Time = 
VAR _SDate = MINX( VALUES('Job Budget'[[Job]] Start Date] ), [[Job]] Start Date] )
VAR _DDate = MAXX( VALUES('Job Budget'[[Job]] Due Date] ), [[Job]] Due Date] )
VAR vDates = SELECTCOLUMNS( FILTER( VALUES( Dates ), Dates[Date] >= _SDate && Dates[Date] <= _DDate && Dates[IsWorkingDay] = TRUE() ), "Date", [Date] )
VAR vJobs = VALUES( 'Job Budget'[[Job]] Job No.] )
VAR vTable =
    ADDCOLUMNS( 
        GENERATEALL( vDates, vJobs),
        "@Allocation",
            VAR myJob = [[Job]] Job No.] 
            VAR _JobS = CALCULATE( MINX( VALUES('Job Budget'[[Job]] Start Date] ), [[Job]] Start Date] ), 'Job Budget'[[Job]] Job No.] = myJob )
            VAR _JobD = CALCULATE( MAXX( VALUES('Job Budget'[[Job]] Due Date] ), [[Job]] Due Date] ), 'Job Budget'[[Job]] Job No.] = myJob )
            VAR DayInContext = COUNTROWS(Dates)
            VAR BudgetedJobDays =  CALCULATE( SUM('Job Budget'[Job Budget Days]), 'Job Budget'[[Job]] Job No.] = myJob )
            VAR BudgetedTime = CALCULATE( SUM('Job Budget'[Estimated Time (HRS)]), 'Job Budget'[[Job]] Job No.] = myJob )
        RETURN
        CALCULATE( IF( HASONEVALUE( Dates[Date] ),
            DIVIDE( DayInContext, BudgetedJobDays, 0 ) * BudgetedTime, 0
        ),  FILTER( VALUES( Dates ), Dates[Date] >= _JobS && Dates[Date] <= _JobD && Dates[IsWorkingDay] = TRUE() ))
    )
RETURN

    SUMX( vTable, [@Allocation] )

.
With this result.

Here’s your sample. DatesBetweenModel.pbix (142.8 KB)
I hope this is helpful.

EnterpriseDNA · January 24, 2021, 7:54pm

Hi @Simon1, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

EnterpriseDNA · January 26, 2021, 1:32am

Hi @Simon1, we’ve noticed that no response has been received from you since the 20th of January. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

Simon1 · January 27, 2021, 9:37pm

Hi Melissa,
Sorry I haven’t got back to you, been away for a few days. Thanks for your help on this.

EnterpriseDNA · January 28, 2021, 3:30am

Hi @Simon1, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!