Show worked hours based on Start and End date parameters

[Anonymous]

So I have 2 tables:

• A calendar one, which I intend to use for filtering
• A table with employee names, when they started/ended working (some may still be working), and the hours they have worked so far

My end goal would be to have a date slicer (using the calendar table) + a filter for the employee name, that would give me the worked hours between those 2 given dates

I think it may not be that hard, but I am just stuck and also could not find a related post about this.

Thank you! 

[v-zhangti]

Hi,@andrebarrande 

You can try the following methods.

1. Create a new calculation column End Date2. If there is an end date, return the end date. If there is no end date, return today's date.

End Date2 = 
IF ( 'Worked hours'[End Date] = BLANK (), TODAY (), [End Date] )

2.Create a calendar table as shown in the figure.

Table =
CALENDAR (
    MINX ( 'Worked hours', [Start Date] ),
    MAXX ( 'Worked hours', [End Date2] )
)

3.Create the measure Worked hours to calculate the working hours excluding weekends.

Worked hours = 
var _ma = max('Table'[Date])
var _mi = MIN('Table'[Date])
var _c = CALENDAR(_mi,_ma)
var _a = countrows(filter(ADDCOLUMNS(_c,"aa",WEEKDAY([Date],2)),[aa]<6))*8
return
_a

Best Regards,

Charlotte Zhang

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

[Anonymous]

Hi @v-zhangti  I appreciate you taking the time!

Unfortunately that does not solve my issue because the formula

1. Assumes everyone is still working (For example, for Robert who stopped working in July, we would need to not count hours for him if the Date is higher than that
2. Assumes everyone works 8 hours a week (In the example I provided you can see that, for example, Paul works 37.5 hours a week)

[amitchandak]

@Anonymous , Try a measure like

Work hour=
var _max1 = maxx(allselected('Date'), 'Date'[Date]) //selected date
var _max2 = Max(Table[End Date])
var _max = min(_max1, _max2)
return
sumx(values(Table[employee name]) , COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Min(Table[Start Date]),_max),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))*8 )

[Anonymous]

Thank you @amitchandak 

It is working perfectly when I move the End Date on the slicer, but it doesn't seem to react when I move the Start Date

For example: This person worked from March 1st to September 5th. Total worked hours is 1080 and the formula is doing it correctly

If I move the End Date, it is reacting accordingly

But if I move the Start Date, it doesn't react