Skip to main content
cancel
Showing results for 
Search instead 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

Reply
BenArnold
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
johnt75
Super User
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
	)

View solution in original post

2 REPLIES 2
johnt75
Super User
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
	)

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.