cancel
Showing results 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

Frequent Visitor

## Measure to average changing working patterns over different time periods

Hello.

I tried to post this yesterday, but can't see it anywhere, so not sure it worked. Apologies if I'm duplicating.

---------------------------------------

Sorry for the slightly unhelpful message subject - it's hard to summarise what I'm trying to do.

I have produced a report that sets targets for the amount of time each employee should spend working on a project. The target is set by month, based on the number of working days in the month and on the proportion of full-time hours that each person works. This was working ok until one person changed their hours part way through the year. To do this, the person has been entered twice on the list of staff details, so it looks like two different people with the same name. Here's an example, where Person 1 and Person 5 have changed their hours part way through April and May respectively - those with no end dates are still employed on the hours shown:

 Table 1 Start End % of full time hours Person 1 01/04/2021 24/04/2023 100% Person 2 01/04/2021 75% Person 3 01/04/2021 50% Person 4 01/04/2021 100% Person 5 01/04/2021 15/05/2023 100% Person 6 01/04/2021 100% Person 1 25/04/2023 50% Person 5 16/05/2023 75%

So person 1 was working full time until 24th April and then from 25th April they reduced their hours to 50%. Person 5 was working fulltime until 15th May and then reduced their hours to 75%.

The next table calculates the % of fulltime hours that each person would have been expected to work over two separate months - April and May. There is one row per person per month:

 Table 2 Period Start Period End Days in period Average % of fulltime hours Person 1 01/04/2023 30/04/2023 30 90% Person 2 01/04/2023 30/04/2023 30 75% Person 3 01/04/2023 30/04/2023 30 50% Person 4 01/04/2023 30/04/2023 30 100% Person 5 01/04/2023 30/04/2023 30 100% Person 6 01/04/2023 30/04/2023 30 100% Person 1 01/05/2023 31/05/2023 31 50% Person 2 01/05/2023 31/05/2023 31 75% Person 3 01/05/2023 31/05/2023 31 50% Person 4 01/05/2023 31/05/2023 31 100% Person 5 01/05/2023 31/05/2023 31 87% Person 6 01/05/2023 31/05/2023 31 100%

In April, Person 1 worked for 24 of the 30 days as a fulltime employee. They then worked the final 6 days as part time (50%). On average, their hours were ((100*24)+(50*6))/30 = 90%. Obviously I need to take into account weekends, etc, but I'm ignoring that for this example to make things a bit simpler.

How can I get Power BI (using DAX) to calculate the value in the final column of table 2? I keep thinking I've got the answer, but then things unravel quite quickly! I want either a measure or a calculated column - don't mind which. I'm assuming a calculated column would be simpler......

Many thanks in advance for your help.

Ben

1 ACCEPTED SOLUTION
Super User

You can create a measure like

``````Avg time worked =
VAR TotalDaysInMonth = SUM( 'Date'[Is Working Day] )
RETURN
SUMX(
'Table',
VAR StartDate = 'Table'[Start]
VAR EndDate = 'Table'[End]
VAR TotalWorked =
CALCULATE(
SUM( 'Date'[Is Working Day] ),
KEEPFILTERS(
DATESBETWEEN( 'Date'[Date], StartDate, EndDate )
)
)
* 'Table'[% of full time hours]
VAR Result = DIVIDE( TotalWorked, TotalDaysInMonth )
RETURN
Result
)``````
2 REPLIES 2
Super User

You can create a measure like

``````Avg time worked =
VAR TotalDaysInMonth = SUM( 'Date'[Is Working Day] )
RETURN
SUMX(
'Table',
VAR StartDate = 'Table'[Start]
VAR EndDate = 'Table'[End]
VAR TotalWorked =
CALCULATE(
SUM( 'Date'[Is Working Day] ),
KEEPFILTERS(
DATESBETWEEN( 'Date'[Date], StartDate, EndDate )
)
)
* 'Table'[% of full time hours]
VAR Result = DIVIDE( TotalWorked, TotalDaysInMonth )
RETURN
Result
)``````
Frequent Visitor

This is a really good solution and helped me with a couple of other measures I've been struggling with. Took me a little while to sort all the relationships, etc, to make it work, but it seems to have done the job.

Thank you very much!

Ben

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.

#### 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.

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

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

Top Solution Authors
Top Kudoed Authors