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
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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