Measure to average changing working patterns over different time periods

[BenArnold]

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

[johnt75 · Accepted solution]

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
	)

[BenArnold]

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