Sum Holiday Hours Only Once Per Person Per Day

[itsme]

I need sum the number of holiday hours for employees. Employees can work on multiple projects at one time, but they will only get paid for 8 holiday hours regardless of how many project they work on.

The data table below shows that John works on Project A and Project B at the same time and there's a holiday that overlaps both projects on 12/24/2021. If you summed all his holiday hours for the dates shown in the table, it would equal 24 hours. In reality, we are only paying him for 16 holiday hours.

Date	Name	Project	Hours	Holiday
12/17/2021	John	A	8	0
12/18/2021	John	A	0	0
12/19/2021	John	A	0	0
12/20/2021	John	A	8	0
12/21/2021	John	A	8	0
12/22/2021	John	B	4	0
12/23/2021	John	B	6	0
12/24/2021	John	B	0	8
12/25/2021	John	B	0	0
12/26/2021	John	B	0	0
12/27/2021	John	B	7	0
12/28/2021	John	B	6	0
12/29/2021	John	B	6	0
12/22/2021	John	A	8	0
12/23/2021	John	A	7	0
12/24/2021	John	A	0	8
12/25/2021	John	A	0	0
12/26/2021	John	A	0	0
12/27/2021	John	A	4	0
12/28/2021	John	A	6	0
12/29/2021	John	A	6	0
12/30/2021	John	A	7	0
12/31/2021	John	A	5	0
1/1/2022	John	A	0	0
1/2/2022	John	A	0	0
1/3/2022	John	A	4	0
1/4/2022	John	A	0	8
1/5/2022	John	A	5	0
1/6/2022	John	A	5	0

I need to create a measure that respects those rules and only sums once per distinct date and employee. Then I will put it into a table or chart that contains month and looks something like this:

Month	Hours	Holiday
December '21	96	8
January '22	14	8
Total	110	16

I have a calendar table that connect to this data table. Both Hours and Holiday are in the same data table.

Please let me know if you have any thoughts. I'm guessing SUMX will be involved but not sure exactly how to structure this.

Thank you!!

[tamerj1 · Accepted solution]

Hi @itsme 

Please try

Holiday Hours =
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[Name],
        'Date'[Date],
        "@Hours", MAX ( 'Table'[Holiday] )
    ),
    [@Holiday]
)

[itsme]

Thank you @tamerj1 ! I believe the last line should be [@Hours] instead of [@Holiday].

[tamerj1]

@itsme 

Yes that is correct