Work and Capacity question

[JC2022]

Hi,

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

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

Organisation	Team	Capacity per day
Category A	Team 1	3,84
Category B	Team 1	29,38
Category C	Team 3	4,8
Category C	Team 1	31,78
Category C	Team 6	8,64
Category D	Team 3	13,44
Category D	Team 4	14,4
Category D	Team 5	17,74
Category D	Team 7	11,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?

[bhanu_gautam · Accepted solution]

@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]