Calculate working hours within a date range

[Anonymous]

Hi there

I have three tables. A basic one with employees, aka

Employee

Aileen

Tom

Barbara

Another one with their workoing hours per day (multiple entries per day are possible)

Employee	Date	working hours
Aileen	2019/01/05	5
Aileen	2019/01/05	3
Aileen	2019/01/06	8
Aileen	2019/01/08	6
Tom	2019/01/05	1
Tom	2019/01/06	2
Tom	2019/01/08	3
Barbara	2019/01/05	3
Barbara	2019/01/06	8
Barbara	2019/01/08	6

And a third one with a working period (from..to). This table is unrelated to the others 

Period	From	To
Period 01	2019/01/01	2019/01/06
Period 02	2019/01/07	2019/01/13
Period 03	2019/01/14	2019/01/20

I want to add a measure to the third one which includes the SUM of working hours of the employees in each period. In a dropdown I then put the employees. If no employee is selected I want to see the SUM of all working hours. If only one/some are selected, I want to see only the selected employees working hours. 

I hope you can help me

Thanks zemi

[MFelix · Accepted solution]

Hi @Anonymous ,

Create the following measure:

Hours =
CALCULATE (
    SUM ( WorkingHours[working hours] );
    FILTER (
        ALL ( WorkingHours[Date] );
        WorkingHours[Date] >= SELECTEDVALUE ( Periods[From] )
            && WorkingHours[Date] <= SELECTEDVALUE ( Periods[To] )
    )
)

Regards,

MFelix

[MFelix · Accepted solution]

Hi @Anonymous ,

Create the following measure:

Hours =
CALCULATE (
    SUM ( WorkingHours[working hours] );
    FILTER (
        ALL ( WorkingHours[Date] );
        WorkingHours[Date] >= SELECTEDVALUE ( Periods[From] )
            && WorkingHours[Date] <= SELECTEDVALUE ( Periods[To] )
    )
)

Regards,

MFelix

[Anonymous]

Hi @MFelix 

Works nice. Thanks.

If I put the measure in a table, I do not get the sum of all rows. Why?

Regrads, zemi

[TomMartens · Accepted solution]

Hey @Anonymous ,

this is a slightly different solution than the one @MFelix provided.

Please be aware that my solution also requires a calendar tarble (though it does not have to be related to one of the tables). 

Create this measure:

Working Hours = 
SUMX(
    VALUES(Period)
    ,CALCULATE(
        SUM(Working[working hours]),
        TREATAS(DATESBETWEEN('Calendar'[Date],'Period'[From],'Period'[To]),Working[Date])
    )
)

Regards,
Tom

[Anonymous]

Thanks @TomMartens !

Grüsse nach Hamburg