cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors