Problem with getting possible working days in one month depending on planned days per employee

[fmueller22]

Hello,

first: my englisch is not so good but I hope I can clearly describe my problem.

I have a Problem and I hope someone can help me with this.

Short:
I want to show how many possible project working days we got in one month/ one quarter dependent on the planned days per employee.

Long:
I cant post my live data but i have replicated the problem with simplified datastructure.
You can download it here.

I have a table called times where employees worked times are in

Date	RessourceNumber	ProjectNumber	Days
31.01.2023	PersonA	ProjectA	10
31.01.2023	PersonB	ProjectB	10
28.02.2023	PersonA	ProjectA	5
28.02.2023	PersonB	ProjectB	5
31.03.2023	PersonA	ProjectA	5
31.03.2023	PersonB	ProjectB	5

I have a second table called projects where I can see how many days the employees are planned in a specific project

ProjectNumber	RessourceNumber	plannedDays
ProjectA	PersonA	100
ProjectB	PersonB	30

I have a third table called worker where the workerinformations are in. Especcially a timefactor. 1 = 100 % / 0,5 = 50 %

WorkerNumber	timefactor
PersonA	1
PersonB	0,5

I have a forth table which is a date table ( Date = CALENDAR(Date(2023,01,01),Date(2023,12,31) )

Date

01.01.2023

02.01.2023

...

31.12.2023

I have some measures
one measure shows me how many days the emploey is planned:
plannedDays = CALCULATE(sum(Projects[plannedDays]))

one measure shows me how many days he has worked:
workedDays = CALCULATE(SUM(times[Days]))

one measure shows me how many days he still have left:
restDays = [plannedDays] - [workedDays]

one measure shows me how many days he is possible to work (calendar)

possibleProjectDaysCalendar = NETWORKDAYS(MIN('Date'[Date]),MAX('Date'[Date]),1) * SUM(worker[timefactor])

I want a measure where I get the possible working days based on the left days he got.

example:
Condition: Q1 2023 is checked in the date slicer

Person A got a project with 100 planned days. he has 65 possible days to work in Q1(Networkingdays). Also he has 80 days left to work on his project.
then there should be 65 shown

Person B got a project with 30 planned days. he has also 32.5 possible days to work in Q1(Networkingdays). He has 10 days left to work on this project
then there should be 10 shown

I got this with an if statemant:
PossibleProjectDaysForecast = IF ([restDays] >= [possibilProjectDaysCalendar], [possibilProjectDaysCalendar],[restDays])

this is fine when I see only one Person.
But if I look at multiple Persons (Person A & B ) then the result ist 90 insted of 75.
I know why. the measure rest days sum all values so rest days result is 90. 90 is greater then 75, so there will be 90 shown. But how I get this to work to get only 75?

My connections are:

Date to Times (Date/Date) one to many
worker to and from Projects (Workernumber/RessourceNumber) one to one
Projectrs to times (ProjectNumber/ProjectNumber) one to many

kind regards

Florian

[fmueller22]

Hi @Anonymous,

i have checked it again and it worked if i only use (SUMX(Worker),[PossibleProjectDaysForecast]). 
Sometimes it can be so easy... 

kind Regards

Florian

[fmueller22]

Hi @Anonymous,

first thank you for your reply.

The solution worked for the example. But it seems, that i have a mistake in my example. I have to simplified it.
So sorry about that. 
I work on a more accurate sample. I hope when i have created it, you can help me again 🙂

Best Regards

Florian

[Anonymous · Accepted solution]

Hi @fmueller22 

You can create a new measure

Measure = SUMX(VALUES(worker[WorkerNumber]),[PossibleProjectDaysForecast])

Output

Best Regards!

Yolo Zhu

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