showing consecutive days worked

[Gedmonston]

Hi, 

I have a data set that contains columns for the amount of hours worked each day of the month like so:

This data also contains a payroll codes for absent staff.

D1 D2 D3 etc

8   10  OF

is there a way to show the maximum number of consecutive days worked?

Any help would be greatly apprieciated

[v-lid-msft · Accepted solution]

Hi @Gedmonston ,

We can create a measure using following formula to meet your requirement:

MaxWorkDay = 
MAXX (
    ADDCOLUMNS (
        FILTER ( 'Table', 'Table'[Payroll] = "OF" ),
        "WorkDay",
        VAR tday = [Date]
        RETURN
            VAR diff =
                DATEDIFF (
                    MAXX (
                        FILTER ( FILTER ( 'Table', 'Table'[Payroll] = "OF" ), [Date] < tday ),
                        [Date]
                    ),
                    tday,
                    DAY
                )
            RETURN
                IF (
                    diff = BLANK (),
                    COUNTROWS ( FILTER ( 'Table', [Date] < tday ) ),
                    diff - 1
                )
    ),
    [WorkDay]
)

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rheiasilvia]

Hi @Gedmonston,

You need to create a new column in you Calendar table first. For do this in the Power Query Editor, click on you date column and click on the menu add column, bottom Date, go to item Day and choose day of the week . After that you shoud add a custom column, in this column you will type a formula that will help you to dived  the weekends for the work days, is something like this:

 if [day of the week]=6 then 0 else if [day of the week]=0 then 0 else 1

*I will named this column Weekends

In you table, which has all the days with the hours, you should split the column D1, D2 ... for use the days; And for the hour column you must change the word OF for 0. To do this in the  Power Query Editor, click on the bottom custom column e type the formula:

 if [Hours]="OF" then 0 else [Hours]

* I will name this formula HoursWorked

Next click on close and apply. In the Manage Relationships, make sure that your table has one relationship with the Calendar table. Then you can create a new measure for sum the hours, this formula will look only for the hours of the day of the week.

Is something like this :

CALCULATE(SUM(yorhourtable[HoursWorked]);FILTER(Calendar;Calendar[Weekends]=1)).

I hope it helped you