Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mizzmeeshelly
New Member

Easy way to format and audit Break Time

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:

DateStartDateTimeEndDateTimeWorkedHoursProviderNameWorkCode
10/26/202010/26/2020 16:5010/26/2020 17:501AMYAdmin
10/26/202010/26/2020 16:4010/26/2020 16:500.17AMYBREAKS
10/26/202010/26/2020 15:4010/26/2020 16:401AMYAdmin
10/26/202010/26/2020 14:4010/26/2020 15:401AMYAdmin
10/26/202010/26/2020 14:1010/26/2020 14:400.5AMYAdmin
10/26/202010/26/2020 13:4010/26/2020 14:100.5AMYAdmin
10/26/202010/26/2020 13:0010/26/2020 13:300.5AMYLUNCH
10/26/202010/26/2020 12:3010/26/2020 12:400.17AMYBREAKS
10/26/202010/26/2020 11:3010/26/2020 12:301AMYAdmin
10/26/202010/26/2020 10:3010/26/2020 11:301AMYAdmin
10/26/202010/26/2020 9:3010/26/2020 10:301AMYAdmin
10/26/202010/26/2020 9:0010/26/2020 9:300.5AMYAdmin
11/8/202011/8/2020 6:4511/8/2020 7:451CARLAdmin
11/7/202011/7/2020 18:0011/7/2020 18:150.25STACYAdmin
11/7/202011/7/2020 13:4011/7/2020 14:100.5ANNAdmin
11/7/202011/7/2020 13:1011/7/2020 13:400.5ANNAdmin
11/7/202011/7/2020 13:1011/7/2020 14:101BARBARAAdmin
11/7/202011/7/2020 12:4011/7/2020 13:100.5BARBARAAdmin
11/7/202011/7/2020 12:1011/7/2020 12:400.5BARBARAAdmin
11/7/202011/7/2020 12:0011/7/2020 12:100.17BARBARABREAKS
11/7/202011/7/2020 11:4011/7/2020 12:401ANNAdmin
11/7/202011/7/2020 11:3011/7/2020 11:400.17ANNBREAKS
11/7/202011/7/2020 11:0011/7/2020 12:001BARBARAAdmin
11/7/202011/7/2020 10:3011/7/2020 11:301ANNAdmin
11/7/202011/7/2020 10:0011/7/2020 11:001BARBARAAdmin
11/7/202011/7/2020 9:3011/7/2020 10:301ANNAdmin
11/7/202011/7/2020 9:0011/7/2020 10:001BARBARA

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.

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

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.

 

mahoneypat_0-1605831420756.png

 

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
mahoneypat
Microsoft Employee
Microsoft Employee

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.

 

mahoneypat_0-1605831420756.png

 

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.

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

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors