Easy way to format and audit Break Time

[mizzmeeshelly]

Hi all,

I'm new to Power BI and hoping all the masters out there can help me figure out to set this up. My excel data contains the following:

**The result I want to see is an easy way to audit for any one who does not have a BREAK added to their schedule for every 3.5hrs in a work day. It's hard to audit for these because the employees do not have a consistent 8hrs work schedule.

This is the example of data source:

Date	StartDateTime	EndDateTime	WorkedHours	ProviderName	WorkCode
10/26/2020	10/26/2020 16:50	10/26/2020 17:50	1	AMY	Admin
10/26/2020	10/26/2020 16:40	10/26/2020 16:50	0.17	AMY	BREAKS
10/26/2020	10/26/2020 15:40	10/26/2020 16:40	1	AMY	Admin
10/26/2020	10/26/2020 14:40	10/26/2020 15:40	1	AMY	Admin
10/26/2020	10/26/2020 14:10	10/26/2020 14:40	0.5	AMY	Admin
10/26/2020	10/26/2020 13:40	10/26/2020 14:10	0.5	AMY	Admin
10/26/2020	10/26/2020 13:00	10/26/2020 13:30	0.5	AMY	LUNCH
10/26/2020	10/26/2020 12:30	10/26/2020 12:40	0.17	AMY	BREAKS
10/26/2020	10/26/2020 11:30	10/26/2020 12:30	1	AMY	Admin
10/26/2020	10/26/2020 10:30	10/26/2020 11:30	1	AMY	Admin
10/26/2020	10/26/2020 9:30	10/26/2020 10:30	1	AMY	Admin
10/26/2020	10/26/2020 9:00	10/26/2020 9:30	0.5	AMY	Admin
11/8/2020	11/8/2020 6:45	11/8/2020 7:45	1	CARL	Admin
11/7/2020	11/7/2020 18:00	11/7/2020 18:15	0.25	STACY	Admin
11/7/2020	11/7/2020 13:40	11/7/2020 14:10	0.5	ANN	Admin
11/7/2020	11/7/2020 13:10	11/7/2020 13:40	0.5	ANN	Admin
11/7/2020	11/7/2020 13:10	11/7/2020 14:10	1	BARBARA	Admin
11/7/2020	11/7/2020 12:40	11/7/2020 13:10	0.5	BARBARA	Admin
11/7/2020	11/7/2020 12:10	11/7/2020 12:40	0.5	BARBARA	Admin
11/7/2020	11/7/2020 12:00	11/7/2020 12:10	0.17	BARBARA	BREAKS
11/7/2020	11/7/2020 11:40	11/7/2020 12:40	1	ANN	Admin
11/7/2020	11/7/2020 11:30	11/7/2020 11:40	0.17	ANN	BREAKS
11/7/2020	11/7/2020 11:00	11/7/2020 12:00	1	BARBARA	Admin
11/7/2020	11/7/2020 10:30	11/7/2020 11:30	1	ANN	Admin
11/7/2020	11/7/2020 10:00	11/7/2020 11:00	1	BARBARA	Admin
11/7/2020	11/7/2020 9:30	11/7/2020 10:30	1	ANN	Admin
11/7/2020	11/7/2020 9:00	11/7/2020 10:00	1	BARBARA	Admin

Is there any way for me to format so I could audit for BREAK codes easier?

I want to make sure all the employees have 1 BREAK for every 3.5hrs and 2 BREAKs for every 7 hours worked in a day.

Thank you.

[mahoneypat · Accepted solution]

Please try this measure expression to get your result.  Put it in a column with your Date and ProviderName columns to get the shown table.  I used Summarize for this so the measure is more robust and you will also get the correct total average.

Breaks per 3.5 Hrs =
VAR _summary =
    ADDCOLUMNS (
        SUMMARIZE (
            Breaks,
            Breaks[Date],
            Breaks[ProviderName]
        ),
        "cHrsWorked",
            CALCULATE (
                SUM ( Breaks[WorkedHours] )
            ),
        "cBreaks",
            CALCULATE (
                COUNT ( Breaks[Date] ),
                Breaks[WorkCode] = "Breaks"
            )
    )
RETURN
    AVERAGEX (
        _summary,
        DIVIDE (
            [cBreaks],
            [cHrsWorked]
        ) * 3.5
    )

Regards,

Pat

[mizzmeeshelly]

Hi Pat,

Thank you for the quick response and taking your time to help me.

I went ahead and added your code to the new measures but the result I get is an error that say "cannot find table "Breaks"

Not sure if I'm missing anything.

[mahoneypat]

That is what I called my table that i made with your sample data.  Replace it with your table name.

Regards,

Pat

[mizzmeeshelly]

Awesome! I got it to work exactly like your table above. It's helpful now to narrow the employees that should be having a break but instead of the returning the average, is there any way for me to replace with returning the number of Breaks they should be taking?

I should have been more clear in the original post but the end result I'm looking to achieve is: 

• Any employees who worked more than 3.5hrs consectively in a day should have one 10mins break added and two breaks for 7 hours worked in a day. (minus the lunch time)

For example: The data above shows Amy having 2 breaks because she worked more than 7 hours consecutively.

• My audit is to catch any employees who worked these hours but did not have the Breaks added.

Again, thank you so much! I really appreciate your help.