Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

2 ACCEPTED SOLUTIONS
MFelix
Super User
Super User

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

4 REPLIES 4
MFelix
Super User
Super User

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Thanks @TomMartens !

Grüsse nach Hamburg

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors