Forum Discussion

BenArnold's avatar
BenArnold
Frequent Visitor
3 years ago
Solved

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 m...
Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    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
	)
johnt75's avatar
johnt75
Super User
3 years ago

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's avatar
    BenArnold
    Frequent Visitor
    3 years ago

    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