IF Statement Not Working

[Anonymous]

Hello guys, I am new to Power BI and trying to figure out how to perform a DAX calculation. Below is a snip of some calculated columns I made: 

So with the help of the forum I figured out how to calculate the total number of hours a particular employee worked on a particular week which is the column titled "Total by Emp & Week." Then I figured out the DAX code to get the "Overtime 1.5", "Overtime 2.0", and "Normal Hours" hour calculations on my own; these show how many overtime and double time hours an employee worked on a particular week. It appears that my "OT 2.0" and "Normal Time" columns are working just fine, the dax is pasted below: 

Total Hours Worked by Particular Employee on Particular Week Dax:

Total by Emp and Week = CALCULATE(SUM(ABCTEMPS[Clocked presence]), ALLEXCEPT(ABCTEMPS,ABCTEMPS[Employee ID],ABCTEMPS[Week]))

Overtime 2.0 Dax: 

OT 2.0 = IF(ABCTEMPS[Total by Emp and Week]>=60, ABCTEMPS[Total by Emp and Week]-60,0)

Normal Time Dax: Normal Time = IF(ABCTEMPS[OT 1.5]=0, ABCTEMPS[Total by Emp and Week],40)

The problem is my "OT 1.5" Dax seems to not work when an employee worked above 60 hours in a week and has double time hours. That Dax is below:

OT 1.5 Dax: 

= IF(AND(ABCTEMPS[Total by Emp and Week]>40, ABCTEMPS[Total by Emp and Week]<60), ABCTEMPS[Total by Emp and Week]-40,0)

If I can get the Dax to work for the "OT 1.5" column, then my "Normal Time" column should return 40 hours for each row. The "OT 1.5" column would show 20 hours for each row. My "OT 1.5" dax seems to work for all rows except for the ones where the hours worked is above 60 hours. Any help is greatly appreciated! Thank you and let me know if you have any questions!

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Anonymous]

It would be very hard to upload a file with no sensitive information, as their is so much sensitive info to cleanse. I think I gave too much info in my original post. The only Dax formula that I need help with is the 

OT 1.5 Dax: 

= IF(AND(ABCTEMPS[Total by Emp and Week]>40, ABCTEMPS[Total by Emp and Week]<60), ABCTEMPS[Total by Emp and Week]-40,0)

If an employee worked 74.17 hours in a week, I need the "OT 1.5" column to show 20 hours (which is the amount of hours between 40 and 60. Right now it shows 0. Any help is greatly appreciated.

[JamesFR06 · Accepted solution]

Hi

It's normal that shows 0 beause 74.17 is greather than 60. If you just want  to calculate for hours >60 change your code like this

= IF(AND(ABCTEMPS[Total by Emp and Week]>40, ABCTEMPS[Total by Emp and Week]>60), 20,0)

and if you want for all calculations:

= IF(AND(ABCTEMPS[Total by Emp and Week]>40, ABCTEMPS[Total by Emp and Week]<60), ABCTEMPS[Total by Emp and Week]-40,IF(ABCTEMPS[Total by Emp and Week]>60,20,0))

[Anonymous]

Your second DAX formula worked like a charm. The OT 1.5 column now shows 20 hours for each row where an employee worked over 60 hours. Thank you!