Showing results for 
Search instead for 
Did you mean: 
Helper II
Helper II

How to use on no. of working days in a month

I made total working days in a month in calendar table. Now I need to use this data to calculate total hours of work delivered in main table based on if a condition is satisifed or not.





FTE of Rate Type Onshore =
    IF (
        [Rate Type] == "Paid",
            IF (
                [office] == "Farm",
                 [Hours delivered] / 157.08 ,
                    IF (
                        [office] == "Boult",
                        [Hours delivered] /127.68 ,
                            IF (
                                [office] == "Global",
                                [Hours delivered] / 136.92,
                                    IF (
                                        [office] == "GOTS",
                                        [Hours delivered] / 131.04,
                                            IF (
                                                [office] == "Daxnd",
                                                [Hours delivered] / 136.92,
                                                    IF (
                                                        [office] == "UK",
                                                        [Hours delivered] / 131.04,
                                                            IF (
                                                                [office] == "UTS",
                                                                [Hours delivered] / 131.04,
                                                                    IF ( [office] == "ANZ", [Hours delivered] / 149.94, 0 )





As the values with which I'm dividing "Hours delieverd "  is for month of Aug, but I want to change it dynamically with every month. I tried creating a measure(not able to create column) and dividing, but it gives me wrong values.
I'm not sure how to use no. of working days or hours in a month to compute final result.

I have a calendar table with working days in it but not sure how to get what I want.




Thanks in advance.

EDIT 1:- I forgot to mention how I got working hours(157.08) in the first place. So, it's like (otal no. of working hours in a day * no. of working days in a month), total no. of working hours in a day is fixed.

EDIT 2 :- @AllisonKennedy , @amitchandak can you guys help?

Helper II
Helper II


The issue-
Data available - 
Fact table has more than 70columns and 200000rows out of which the table I need to create in BI is concerened with only Service Period, Rate Type, LSA , Type of work, charge type and no. of hours delieverd.


LSAService PeriodRate TypeHours DelieverdType of workCharge type
LSA101-07-2014Onshore1Service for assistanceFixed

Desired Output-

I need to create a table which calculates the no. of hours delivered based on Rate Type, Charge Type, Type of Work on the basis of Month and Year and LSA.
Example- Where the data changes dynamically with month and year

LSAFTE of Rate Type OnshoreFTE of Rate Type OffshoreFTE of Rate Type BlendedFTE for Application EnhancementFTE of work type Variable


Now these values are calculated using-
No. of hours of work delivered in a month(with conditions) divided by total no. of available working hours in a month. which is different for different LSAs. Example-
Total working hours in a month = no. of working hours in a dayfixed but different for Onshore and Offshore) * no. of working days in a month(changes dynamically)
LSA1(working hours in a month for LSA1) = 6.24 * 21(131.04) OR,
LSA2(working hours in a month for LSA2) = 6.52* 21(136.92)


What has be done so far-

Completed the table and logic except the final calculation part(wrong values I got).
Thing where I'm stuck-
I have devised logic for calculating no. of working days (a measure in fact table), using calendar table, but when I'm using it to divide it with sum of professional hours delivered. It does not produce an expected output. 
The code I'm using is given above.
Everything is working perfectly fine sperately, but when I'm using measure for division the output produced is wrong.

Help would be much appreciated I'm stuck here for last 2 days(just a beginner).
Thanks in advance for helping.

Super User III
Super User III

@R_S-13697  Where does the 131.04, 136.92, etc come from? 


Please remind me what your Fact (main table) looks like. 


You should be able to create a measure for the denominator: 


[Working Hours] = SUMX(Date, Date[Working Days]*8) 


Where 8 is the fixed number of working hours in a day. 


As long as you use the Date[Date] in the visual, you should have no trouble using that [Working Hours] measure in the denominator of your measure calculation.


You also may consider using SWITCH instead of so many nested IF statements. 

?? Check out my March Madness Report??

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos. ?

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

@AllisonKennedy  I tried creating measure as you suggested, but it's not working correctly. It is producing same output as before.

131.04 = 6.08(working hours in a day) * 21(August 2020 -21 working days)

Helpful resources

PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!


The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.


Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.