Format calculation for easy audit

[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 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 that they work. 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

This is the output I organize on Power BI:

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

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

Thank you.

 

[macfuller]

Do you need to calculate how many break hours they had in a workday or just that they had 2 breaks?
Do break hours count toward working hours? Lunch hours?

Assuming you have the dates as columns and ProviderName as rows so we don't have to get fancy on the measures...

[Sum of Worked Hours] := SUM(Source[WorkedHours]) // Counts the break hours too
Otherwise
[Sum of Worked Hours] := CALCULATE ( SUM(Source[WorkedHours]), Source[WorkCode] <> "BREAKS")
[Number of Breaks] := CALCULATE ( COUNTROWS(Source[ProviderName]), Source[WorkCode] = "BREAKS")

[Took All Breaks] :=
IF ( ISBLANK([Sum of Worked Hours]), BLANK(),
IF (
    [Sum of Worked Hours] <= 3.5
        && [Number of Breaks] = 1,
    BLANK (),
    IF (
        [Sum of Worked Hours] <= 7
            && [Number of Breaks] = 2,
        BLANK (),
        "Break Error"
    )
)
)

If you use the [Took All Breaks] measure this should highlight who didn't take the appropriate number of breaks - blank if they're OK, or "Blank Error" if they had a problem on that date. You can adjust the logic if someone is allowed more than one break each 3.5 hours.