Sum hours based on day, then create new column with IF function

[Coryanthony]

Hello, 

I want to sum up hours worked base on day, then create a column ($Allowed) base on the sum of hours.

$ Allow = CALCULATE(SUM(Timesheet[Number (unit)]), IF(Timesheet[Day of Week] >=0 && Timesheet[Day of Week] <=4 && Timesheet[Number (unit)] >10, 20,0))

[Ashish_Mathur]

Hi,

Show the expected result very clearly.  Share the download link of the PBI file.

[Coryanthony]

thank you for your input Ashish. I am learning more about community.powerbi. 

If you have any suggestion, please feel free to share. Thank you

[v-tangjie-msft]

Hi @Coryanthony ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.  

(2) We can create a calculated column.

$Allowed = 
var _sum = CALCULATE(SUM('Table'[number (unit)]),FILTER(ALLEXCEPT('Table','Table'[employee id],'Table'[date]),'Table'[number (unit)]))
return 
IF(_sum>10&&'Table'[Day of Week]>=0&&'Table'[Day of Week]<=4,20,0)

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider  Accept it as the solution  to help the other members find it more quickly. 

[Coryanthony]

Hi Neeko,

Thank you so much for your time and well written explanation, it honestly means a lot. 

Your solution worked! Only issue - its currently allowing $20 multiple times for same day. If you look at the snippet you provided for date "2022-12-13", it duplicated the $20. I assume the only work around is to delete the duplicates? 

I am faily new to power BI, but doing my very best to learn. If there are any tools/courses/classes you can suggest, that would be extremely awesome. If you also know of any personal tutors, please please share.

Thank you.

[v-tangjie-msft · Accepted solution]

Hi @Coryanthony ,

This is due to the nature of calculated columns, and you can create a table visual to show.

You can also create a measure.

measure_Allowed = 
var _sum = CALCULATE(SUM('Table'[number (unit)]),FILTER(ALLEXCEPT('Table','Table'[employee id],'Table'[date]),'Table'[number (unit)]))
return 
IF(_sum>10&&max('Table'[Day of Week])>=0&&MAX('Table'[Day of Week])<=4,20,0)

Please refer to my PBIX file.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Coryanthony]

one last thing Neeko, if i need to add more IF/logical statement to this such as, "

IF(_sum>=5 <8 &&'Timesheet'[Day of Week]>=5 ,15,0)

IF(_sum>=8 &&'Timesheet'[Day of Week]>=5 ,30,0)" 

These statement simply allow $15 if worked 5-7.999 hours on weekend. The other allows $30 if 8+ hrs are worked on the weekend.

I will need to add another logical statement for holiday days. So If sum is >4 and on 12/25/2022 then $30.

Thank you again.

[Coryanthony]

Please help if all possible. Thank you