Forum Discussion
Sum hours based on day, then create new column with IF function
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.
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.
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.
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.