Distribute remaining budget hours over workingdays between start and enddate project

[BobKoenen]

HI all, 

I want to have a measure that gives me the remaining budget per project distributed evenly over the working days from today till the project deadline. If a project has not yet started the measure should give me the budgetted hours distributed evenly over the workingdays between [start date] and [end date]

My data looks like this:

Projecttable:

Project	Start date	end date	Total budgetted hours
A	1-8-2021	13-8-2021	40
B	9-8-2021	13-8-2021	40

Hours table

Project	date	Hours
A	2-8-2021	2
A	2-8-2021	1
A	3-8-2021	1

Date table

date	workingday
1-8-2021	no
2-8-2021	Yes
3-8-2021	Yes
4-8-2021	Yes
5-8-2021	Yes
6-8-2021	Yes
7-8-2021	no
8-8-2021	no
9-8-2021	yes
10-8-2021	yes
11-8-2021	yes
12-8-2021	yes
13-8-2021	yes

My desired result looks like this based on that today is 3-8-2021:

Project A (40 hours - 4 hours) / (remaining working days 9)= 4 hours per day
Project B  40 hours/ 5 workingdays = 8 hours per day

Result table

Date	Hours
3-8-2021	4
4-8-2021	4
5-8-2021	4
6-8-2021	4
9-8-2021	12
10-8-2021	12
11-8-2021	12
12-8-2021	12
13-8-2021	12

[v-xiaotang]

Hi @BobKoenen 

use this measure

Measure = 
var _totalHourA=CALCULATE(MIN(Projecttable[Total budgetted hours]),Projecttable[Project]="A")
var _totalHourB=CALCULATE(MIN(Projecttable[Total budgetted hours]),Projecttable[Project]="B")
var _currentDateA=CALCULATE(MAX('Hours table'[date]),ALL('Hours table'))// get current date 2021/8/3
var _startdate=CALCULATE(MIN(Projecttable[Start date]),Projecttable[Project]="A")//get 2 date range, _startdate to _middate, _middate to _enddate
var _middate=CALCULATE(MIN(Projecttable[Start date]),Projecttable[Project]="B")//
var _enddate=CALCULATE(MIN(Projecttable[end date]),Projecttable[Project]="B")//

var _DaysRemainingA=CALCULATE(COUNTROWS('Date table'),FILTER(ALL('Date table'),'Date table'[workingday]="Yes"&&'Date table'[date]>=_currentDateA))// get remaining working days 9 of A
var _DaysRemainingB=CALCULATE(COUNTROWS('Date table'),FILTER(ALL('Date table'),'Date table'[workingday]="Yes"&&'Date table'[date]>=_middate&&'Date table'[date]<=_enddate))// get working days 9 of B
var _sumHourA=SUMX('Hours table','Hours table'[Hours])//get 4 hours of A

var _evenHour1=DIVIDE((_totalHourA-_sumHourA),_DaysRemainingA) // get (40-4)/9
var _evenHour2=DIVIDE(_totalHourB,_DaysRemainingB) // get 40/5
return IF(MIN('Date table'[workingday])<>"Yes"||MIN('Date table'[date])<_currentDateA,BLANK(),IF(MIN('Date table'[date])<_middate,_evenHour1,_evenHour2+_evenHour1))

result:

See sample file attached bellow.

Best Regards,

Community Support Team _Tang

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

[BobKoenen]

Hi @v-xiaotang 

So many thanks this looks like a really eloborate formula. Only in reality there are 100+ projects and new ones are added each month. Is it possible to edit the formula to make it dynamic so I will not need to add new variables every time that a new project is added. 

[v-xiaotang · Accepted solution]

Hi @BobKoenen 

try this

test1 =
CALCULATE (
    SUM ( Projecttable[Total budgetted hours] ) - SUM ( 'Hours table'[Hours] ),
    GROUPBY ( Projecttable, Projecttable[Project] )
)

test2 =
[test1]
    / MINX (
        {
            COUNTROWS (
                FILTER (
                    ALL ( 'Date table' ),
                    'Date table'[date] >= MAX ( Projecttable[Start date] )
                        && 'Date table'[date] <= MAX ( Projecttable[end date] )
                        && 'Date table'[workingday] = "Yes"
                )
            ),
            COUNTROWS (
                FILTER (
                    ALL ( 'Date table' ),
                    'Date table'[date] >= DATE ( 2021, 8, 3 )// you can replace  DATE ( 2021, 8, 3 ) with Today() later.
                        && 'Date table'[date] <= MAX ( Projecttable[end date] )
                        && 'Date table'[workingday] = "Yes"
                )
            )
        },
        [Value]
    )

test3 =
VAR _currentDateA =
    CALCULATE ( MAX ( 'Hours table'[date] ), ALL ( 'Hours table' ) )
RETURN
    IF (
        MIN ( 'Date table'[workingday] ) <> "Yes"
            || MIN ( 'Date table'[date] ) < _currentDateA,
        BLANK (),
        SUMX (
            FILTER (
                ALLEXCEPT ( Projecttable, Projecttable[Project] ),
                Projecttable[Start date] <= MAX ( 'Date table'[date] )
                    && MAX ( 'Date table'[date] ) <= Projecttable[end date]
            ),
            [test2]
        )
    )

result

Best Regards,

Community Support Team _Tang

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