Expected hour in a month

[ashik1992]

Hi folks , Need a help.

i want to calculate the expected hours in each month for each consultant
it should be in a way that,we should consider the holidays and flexi in the respective moonth
for example , January had 1 holiday and 1 flexi oliday
so expected hours in jan= 21*8- 8( holiday hour)+2.5( 1flexi's compensation)
basically , expected hours= no of working days (excluding holidays)*8 -(8 * Number of holidays)+(number of flexi   * 2.5)
this is what im trying to achieve

Invoice table contains employee details and actual hours worked and those details

[amitchandak · Accepted solution]

@ashik1992 first of in a date table update holidays and flexi hours

work day =

var _max = maxx(filter(Holiday, Holiday[Date] = Date[Date]), Holiday[Type])

return

Switch(true(),

not(isblank(_max)) , _max,

weekday([Date],2) >=6, "Holiday")

Noy you can create a measure

work hour= 8*count(Date[Date]) -8 * Countx(filter(Date, Date[Work day] ="Holiday"),[Date]) +2.5 * Countx(filter(Date, Date[Work day] ="Flexi"),[Date])

You can use this count of employee

Employee hour = [Count Employee] *[Work Hour]

also, check

How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA

[ashik1992]

i made some changes it worked thank you so much

[ashik1992]

marking flexi and holiday hour means? like this ?

[amitchandak]

@ashik1992 , merge this with your date table using merge in power query or create a new column in DAX using this

[ashik1992]

what is the dax query sir

[ashik1992]

Holiday is my date table and should i merge Holiday with Invoice table ?