Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ashik1992
Helper I
Helper I

Expected hour in a month

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

 

ashik1992_0-1644811842811.pngashik1992_1-1644811872537.pngashik1992_2-1644812035451.png

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

 

View solution in original post

6 REPLIES 6
amitchandak
Super User
Super User

@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

 

i made some changes it worked thank you so much

marking flexi and holiday hour means? like this ?

 

ashik1992_0-1644815199339.png

 

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

what is the dax query sir

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.