Hourly breakdown of staff including breaks

wailey1 · ‎11-27-2023

Hi

I need to breakdown the start and finish times of our staff into hours so i can compare hourly sales vs hourly staff costs. I have got to a point where i have the hour breakdown from the signing in and signing out times, but cant work out how to add the breaks (there are up to 4 a day for some lucky staff) I think the original formula i have used is very long winded and can probably be simplified as well, but as a novice i went with what i could do!!

Formula used is;

HourBreakdown = IF('CHAMPS LABOUR ACTUALS 23'[SignedInTime]=0,0,IF('CHAMPS LABOUR ACTUALS 23'[SignedInTime]>'Champs Labour Actuals 23'[Hours],0,IF('Champs Labour Actuals 23'[SignedOutTime]>'Champs Labour Actuals 23'[Hours],IF(('Champs Labour Actuals 23'[Hours]-'CHAMPS LABOUR ACTUALS 23'[SignedInTime])>1/24,1/24,'Champs Labour Actuals 23'[Hours]-'CHAMPS LABOUR ACTUALS 23'[SignedInTime]),IF('Champs Labour Actuals 23'[SignedOutTime]>'CHAMPS LABOUR ACTUALS 23'[Hours],'Champs Labour Actuals 23'[SignedOutTime]-'CHAMPS LABOUR ACTUALS 23'[Hours],0))))*60*24

From this data;

The break data looks like this;

Would ideally like to have just the one column with all the information calculated.

Any ideas please?

Thanks

v-tangjie-msft · ‎11-29-2023

Hi @wailey1 ,

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

(1) This is my test data.

(2) We can create calculated columns.

breakhour = HOUR([Break1Length]+[Break2Length]+[Break3Length]+[Break4Length])

workhour- breakhour = 
var _work = HOUR([SignedOutTime]-[SignedInTime])
var _break=CALCULATE(SUM('Table (2)'[breakhour]),FILTER('Table (2)',[id]=EARLIER('Table'[id])))
return _work-_break

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

wailey1 · ‎11-29-2023

hi

Thanks for your response but that wasnt what i was after. I have done a pbix of my raw data, but i cant work out how to attach it to this reply!! Apologies for my ignorance, but can you show me how, then i can explain what i need.

Thanks