Forum Discussion

JC2022's avatar
JC2022
Helper III
2 years ago
Solved

Work and Capacity question

Hi,

I have the following table with the cumulative sum of hours of work.

 

MonthYear Jul-24Aug-24Sep-24Oct-24Nov-24Dec-24Jan-25
CategoryTeamCumulative HoursCumulative HoursCumulative HoursCumulative HoursCumulative HoursCumulative HoursCumulative Hours
Category ATotal                               43,30                               93,77                            141,95                            194,71                            235,56                            257,00                            257,00
Category ATeam 1                               16,18                               35,96                               54,84                               75,52                               89,00                               89,00                               89,00
Category ATeam 2                               21,30                               44,88                               67,39                               92,05                            114,56                            136,00                            136,00
Category BTotal                            103,43                            213,95                            291,28                            347,95                            399,68                            412,00                            412,00
Category BTeam 6                                  2,00                                  2,00                                  2,00                                  2,00                                  2,00                                  2,00                                  2,00
Category BTeam 1                               10,00                               10,00                               10,00                               10,00                               10,00                               10,00                               10,00
Category BTeam 5                                  0,16                                  0,36                                  0,55                                  0,76                                  0,95                                  1,00                                  1,00
Category CTotal                        1.158,28                        1.281,55                        1.285,24                        1.288,94                        1.292,30                        1.295,50                        1.295,50
Category CTeam 6                                  3,00                                  3,00                                  3,00                                  3,00                                  3,00                                  3,00                                  3,00
Category CTeam 1                            637,13                            720,65                            720,98                            721,00                            721,00                            721,00                            721,00
Category CTeam 5                            149,38                            152,90                            156,26                            159,94                            163,30                            166,50                            166,50
Category CTeam 7                               19,00                               19,00                               19,00                               19,00                               19,00                               19,00                               19,00
Category CTeam 2                                  3,00                                  3,00                                  3,00                                  3,00                                  3,00                                  3,00                                  3,00
Category DTotal                        6.186,91                        8.082,38                        9.329,76                     10.634,62                     11.699,76                     12.294,45                     12.435,35
Category DTeam 3                            563,89                            924,30                        1.268,34                        1.645,14                        1.989,17                        2.153,00                        2.153,00
Category DTeam 4                        1.190,72                        1.389,28                        1.501,12                        1.621,15                        1.728,66                        1.781,25                        1.781,25
Category DTeam 5                        1.278,46                        1.558,46                        1.720,70                        1.884,54                        1.937,07                        1.986,91                        1.987,00
Category DTeam 7                            784,79                        1.057,99                        1.175,39                        1.280,13                        1.369,25                        1.402,83                        1.402,83
Category DTeam 2                               45,81                               55,96                               59,68                               60,25                               60,25                               60,25                               60,25

 

This cumulative hours is created with the measure:

Cumulative Hours =
VAR CurrentDate = MAX('Calendar'[Date])
RETURN
    CALCULATE(
        SUM('Plan Task per Date'[Hours to do per workday]),
        FILTER(
            ALLSELECTED('Calendar'),
            'Calendar'[Date] <= CurrentDate
            ))
 
But I also have table with the capacity to do the work per working day.
OrganisationTeamCapacity per day
Category ATeam 13,84
Category BTeam 129,38
Category CTeam 34,8
Category CTeam 131,78
Category CTeam 68,64
Category DTeam 313,44
Category DTeam 414,4
Category DTeam 517,74
Category DTeam 711,64

 

Now I would like to use this capacity table to see the amount of hours of work and the capacity. The difficulty is there is no relation between the capacity table and the calendar table (date). 

How can this problem be solved?

 
  • JC2022 , Try to create new measure for Cumulative Capacity

    Cumulative Capacity =
    VAR CurrentDate = MAX('Calendar'[Date])
    VAR WorkingDays =
    CALCULATE(
    COUNTROWS('Calendar'),
    FILTER(
    ALLSELECTED('Calendar'),
    'Calendar'[Date] <= CurrentDate
    )
    )
    RETURN
    SUMX(
    'CapacityTable',
    'CapacityTable'[Capacity per day] * WorkingDays
    )

     

    Then subtract it Cumulative Hours from it

     

    Remaining Capacity = [Cumulative Capacity] - [Cumulative Hours]

1 Reply

  • JC2022 , Try to create new measure for Cumulative Capacity

    Cumulative Capacity =
    VAR CurrentDate = MAX('Calendar'[Date])
    VAR WorkingDays =
    CALCULATE(
    COUNTROWS('Calendar'),
    FILTER(
    ALLSELECTED('Calendar'),
    'Calendar'[Date] <= CurrentDate
    )
    )
    RETURN
    SUMX(
    'CapacityTable',
    'CapacityTable'[Capacity per day] * WorkingDays
    )

     

    Then subtract it Cumulative Hours from it

     

    Remaining Capacity = [Cumulative Capacity] - [Cumulative Hours]