total hours worked in last year

[thisguy]

I have an employee dataset just under 2000 rows that includes the following fields: 

EMP Num	start date	End date	Expected Hours
#1	1/01/2021	30/06/2021	38
#2	1/03/2021		25
#3	1/01/2019		40

i want to calculate the total expected hours in the last year for the full workforce and am having difficulty working out how to do this.  I'm not sure if i Should try and do this as a measure or calculated column.

I come from an excel background so i would normally step out in columns like so:  

HrsPerDay = Expected Hours / 5

StartCalcDate = Max of start date and date 1 year ago. 

EndCalcDate = Min of End date and today (or today if blank)

Eligibledays = networkdays between StartCalcDate & EndCalcDate 

workhours = Eligibledays * HPD

[amitchandak · Accepted solution]

@thisguy , refer this HR blog in current employee use per day hours and sum it for total hour

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

example

Total Hour = CALCULATE(Sumx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[hour per day ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

[thisguy]

thanks, exactly what i was after 🙂