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
Anonymous
Not applicable

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
Anonymous
Not applicable

@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.

AllisonKennedy
Super User
Super User

@Anonymous  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. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

@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
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.