cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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.

 
 
 
 
 

Capture.PNG

 

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?

3 REPLIES 3
Helper I
Helper I

@AllisonKennedy 

Hi,
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.

Example-

LSAService PeriodRate TypeHours DelieverdType of workCharge type
LSA101-07-2014Onshore1Service for assistanceFixed
LSA201-12-2015Onshore0.5ProjectFixed
LSA301-06-2019Offshore2ProjectVariable
LSA201-09-2020Onshore12EnhancementVariable
LSA401-06-2019Offshore2MaintenanceFixed


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
LSA125.377.87.10.04.9
LSA226.41.623.82.661.1
LSA34.00.968.60.00.0
LSA41.346.220.90.023.7

 

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. 


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. 


www.excelwithallison.com

@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

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

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.

Top Solution Authors
Top Kudoed Authors