Counting number of days employee was active during a period

[Anonymous]

I am trying to count the number of employees that were active during a period. I define a person as active in the year if they were around for at least 50% of the days, based purely on their start and end dates. For example, if a person started 2/1/2018 and left 11/1/2018, I would still like to count him in our 2018 tally because he was around for 274 out of the 365 days. 

In general, the formula would be:

Count Employee if (Days Active in Period)/(Days in Period) >= 50%

Days Active in Period = MIN(Period End, End Date) - MAX(Period Start, Start Date)

Days in Period = Period End - Period Start

I am having trouble wrapping my head around how to do the 'Days Active in Period' piece in Power BI.

I have done the calculations using the logic above in Excel:

	Period Start	Period End		Count threshold
	1/1/2018	12/31/2018		50%
	Start Date	End Date	Days active in Period	Days in Period	% Active in Period	Count Employee?
Bob	2/1/2018	11/1/2018	274	365	75%	Y
John	6/1/2018	11/1/2018	154	365	42%	N
Steve	6/1/2018		214	365	59%	Y
Joe	6/1/2017	11/1/2018	305	365	84%	Y
Alice	10/1/2018		92	365	25%	N
Nancy	3/1/2018		306	365	84%	Y
Kate	11/1/2018		61	365	17%	N

Thank you in advance for your help once more ... slowly getting my handle on this 🙂 

[Anonymous · Accepted solution]

Figured it out thanks to a previous solution from @v-shex-msft 

Days Active in Period = 
var period_start = FIRSTDATE(ALLSELECTED('Calendar'[Date]))
var period_end = LASTDATE(ALLSELECTED('Calendar'[Date]))
var Start_Date = max(Employees[Start Date])
var End_Date = max(Employees[End Date])
return
DATEDIFF(MAX(period_start, Start_Date), if(End_Date=blank(), period_end, min(End_Date, period_end)), DAY)

[Anonymous · Accepted solution]

Figured it out thanks to a previous solution from @v-shex-msft 

Days Active in Period = 
var period_start = FIRSTDATE(ALLSELECTED('Calendar'[Date]))
var period_end = LASTDATE(ALLSELECTED('Calendar'[Date]))
var Start_Date = max(Employees[Start Date])
var End_Date = max(Employees[End Date])
return
DATEDIFF(MAX(period_start, Start_Date), if(End_Date=blank(), period_end, min(End_Date, period_end)), DAY)

[Anonymous]

Can anyone explain how this code works? Especially the four 'var' lines. 

I am especially confused on how the followings lines of code works:

var Start_Date = max(Employees[Start Date])
var End_Date = max(Employees[End Date])

Thank you! 

[v-yuta-msft · Accepted solution]

@Anonymous ,

In a measure, you need to use aggregation function to achieve the result(not like calculate column), var Start_Date = max(Employees[Start Date]) and var End_Date = max(Employees[End Date]) just means to save current row value to the variable. Every time a new row has been iterated, a new row value will be saved to the variable.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.