Distribute Budget (Hours / Cost) between Start and Finish Dates

[malvaro]

Hi to all.

I saw some variations of this question asked before but none of them represented exactly the scenario I need to solve.

I use a Project Scheduling software and I'm able to connect to its database getting information like tasks, budgets, start and finish dates (among many other things). With that, I can build Tasks (Dimension) and Start-Finish (Fact) tables as follows:

I need to be able to distribute the budgeted hours evenly over time (no matter what kind of time slicing I choose) in order to get something like this (the actual figures will vary depending on the number of days of visible month):

I'm really trying hard to get this with one measure. Don't want to create rows in Power Query for each missing day between dates as I may have 20.000 tasks with an average duration of 90 days which would mean almost 2.000.000 additional rows.

Thanks in advance for any help and hope I've been clear enough!

[draxicle1]

Hi @malvaro and @v-yanjiang-msft 

I was researching this topic for a very similar requirement I have to yours (this is my post -> Measure for Start and End dates - Summing Mean hou... - Microsoft Fabric Community)

and I came across your post.  I hope you don't mind, as it is a few years old.

I too was hoping to create a measure that would place a "mean hours used per day" between the start and end dates, without having the need to create a table with every date.

I am intrigued to know if your solution allowed you to cut across any period (days/months/ quarters) to view the sum of hours budgeted across all activities.

Thanks and regards,

[v-yanjiang-msft · Accepted solution]

Hi @malvaro ,

According to your description, here's my solution.

1.Create a Month table. Then change the column name to Month.

Month = UNION(VALUES('Start-Finish'[Start]),VALUES('Start-Finish'[Finish]))

2.Create a measure.

Measure =
VAR _Days =
    IF (
        MONTH ( MAX ( 'Start-Finish'[Start] ) ) < MONTH ( MAX ( 'Month'[Month] ) ),
        SWITCH (
            TRUE (),
            MONTH ( MAX ( 'Start-Finish'[Finish] ) ) > MONTH ( MAX ( 'Month'[Month] ) ), DAY ( EOMONTH ( MAX ( 'Month'[Month] ), 0 ) ),
            MONTH ( MAX ( 'Start-Finish'[Finish] ) ) = MONTH ( MAX ( 'Month'[Month] ) ),
                DATEDIFF (
                    EOMONTH ( MAX ( 'Month'[Month] ), -1 ),
                    MAX ( 'Start-Finish'[Finish] ),
                    DAY
                )
        ),
        IF (
            MONTH ( MAX ( 'Start-Finish'[Start] ) ) = MONTH ( MAX ( 'Month'[Month] ) ),
            DATEDIFF (
                MAX ( 'Start-Finish'[Start] ),
                EOMONTH ( MAX ( 'Month'[Month] ), 0 ),
                DAY
            )
        )
    )
VAR _TotalDays =
    DATEDIFF ( MAX ( 'Start-Finish'[Start] ), MAX ( 'Start-Finish'[Finish] ), DAY )
RETURN
    DIVIDE ( _Days, _TotalDays ) * MAX ( 'Tasks'[Budgeted Hours] )

Here, I use the exact number of days when I calculate the progress of each month. When the start or end day is not 15th, this would be more accurate.

In the matrix, put the new Month column in Columns and  the measure in Values, get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.