cancel
Showing results for
Did you mean:

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

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)

 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

2 ACCEPTED SOLUTIONS
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

Proud to be a Super User!

Check out my blog: Power BI em Português

Super User

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
4 REPLIES 4
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

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?

Super User

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

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors