Forum Discussion

fmueller22's avatar
fmueller22
Frequent Visitor
2 years ago
Solved

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

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

DateRessourceNumberProjectNumberDays
31.01.2023PersonAProjectA10
31.01.2023PersonBProjectB10
28.02.2023PersonAProjectA5
28.02.2023PersonBProjectB5
31.03.2023PersonAProjectA5
31.03.2023PersonBProjectB5


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

ProjectNumberRessourceNumberplannedDays
ProjectAPersonA100
ProjectBPersonB30

 

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

WorkerNumbertimefactor
PersonA1
PersonB0,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

  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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.

  • fmueller22's avatar
    fmueller22
    Frequent Visitor

    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

  • fmueller22's avatar
    fmueller22
    Frequent Visitor

    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