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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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