cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

1 ACCEPTED SOLUTION
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

6 REPLIES 6
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

Helper I

i made some changes it worked thank you so much

Helper I

marking flexi and holiday hour means? like this ?

Super User

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

Helper I

what is the dax query sir

Helper I

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors