Forum Discussion
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
| 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)
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
- Anonymous2 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
- AnonymousNot 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.
- fmueller22Frequent 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
- fmueller22Frequent 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 RegardsFlorian