cancel
Showing results for
Did you mean:
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.

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 II

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-

 LSA Service Period Rate Type Hours Delieverd Type of work Charge type LSA1 01-07-2014 Onshore 1 Service for assistance Fixed LSA2 01-12-2015 Onshore 0.5 Project Fixed LSA3 01-06-2019 Offshore 2 Project Variable LSA2 01-09-2020 Onshore 12 Enhancement Variable LSA4 01-06-2019 Offshore 2 Maintenance Fixed

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

 LSA FTE of Rate Type Onshore FTE of Rate Type Offshore FTE of Rate Type Blended FTE for Application Enhancement FTE of work type Variable LSA1 25.3 77.8 7.1 0.0 4.9 LSA2 26.4 1.6 23.8 2.6 61.1 LSA3 4.0 0.9 68.6 0.0 0.0 LSA4 1.3 46.2 20.9 0.0 23.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).

Super User III

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

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.

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

www.excelwithallison.com

Helper II

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

Announcements