Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |