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
dcormiernj
Helper I
Helper I

Need to normalize average hours taking into account holidays

I need help displaying avg hours vs avg hours normalized for holidays.  I have this working for week but doesnt work drilling up and down to month and year.  I feel like I am doing this in a very inefficient way.  Any suggestions?  thank you

 

I tried to attach below the details i think others would need to understand what i am asking.

 

I have a measure that calculates average weekly hours normalized for holidays:

Avg Wkly BillHrs Hol Adjustment = 'Measure Financials'[Avg Wkly Bill Hours]*calculate(5/(5-[Holiday count by WEDate]))
this works perfectly when i am displaying week end date with avg hours and avg hours normalized. pasted sample below
Visual sample:
dcormiernj_0-1657631572142.png

 

The problem is when i want to drill up to month and then year. the formula i am using is based on holidays by week.

Is there a better way to calculate this so it can drill up and down through year, month and week?

 

My main data table contains: Emp name, week end date, billable hours as well as other columns. the formulas and other tables im using are below

i can easily get average billable hours by simply doing AVG(billable hours). and the weekly holiday normalization works great.

Avg Wkly BillHrs Hol Adjustment = 'Measure Financials'[Avg Wkly Bill Hours]*calculate(5/(5-[Holiday count by WEDate]))
Holiday count by WEDate = sumx('WE Dates Holiday count','WE Dates Holiday count'[Hol Count])
 
WE Dates Holiday count table
dcormiernj_1-1657631969643.png

 

 
 

 

 
 
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@dcormiernj , Are you trying to calculate business hours or trying to allocate those?

 

if try to calculate

https://exceleratorbi.com.au/calculating-business-hours-using-dax/

View solution in original post

2 REPLIES 2
dcormiernj
Helper I
Helper I

No im not trying to calculate business hours.  we have avg weekly billable hours which in most cases is a subset of business hours.  but i dont care if it is business i just care if it is billable.  we have total business hours from which i easily calulate the weekly average.  then if that week had one holiday then the normalized avg weekly hours would be calculated as   Avg weekly Bill hours * 5/4  <--  5 days a week divided by non holiday days.  if 2 holidays then mulitple by 5/3.  examples are

weekly avg = 20,  1 holiday in the week,  avg normalized for holidays = 20*5/4 = 25

 

amitchandak
Super User
Super User

@dcormiernj , Are you trying to calculate business hours or trying to allocate those?

 

if try to calculate

https://exceleratorbi.com.au/calculating-business-hours-using-dax/

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.

Top Solution Authors