Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate working hours within a date range

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)

EmployeeDateworking hours
Aileen2019/01/055
Aileen2019/01/053
Aileen2019/01/068
Aileen2019/01/086
Tom2019/01/051
Tom2019/01/062
Tom2019/01/083
Barbara2019/01/053
Barbara2019/01/068
Barbara2019/01/086

 

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

PeriodFromTo
Period 012019/01/012019/01/06
Period 022019/01/072019/01/13
Period 032019/01/142019/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

Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    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

  • TomMartens's avatar
    TomMartens
    7 years ago

    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

     

4 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    7 years ago

    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

    • TomMartens's avatar
      TomMartens
      Icon for Super User rankSuper User
      7 years ago

      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's avatar
      Anonymous
      Not applicable
      7 years ago

      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