Counting series of consecutive days pr person (pr month, week etc)

[REGHnoob]

Hi guys

I am having no succes. So help...

I need to show how many consecutive days off/days of work each person has.

So for instance if a person during the past year has had consecutive days off  in series ranging from 1 to 6 I need to show the number of times this person has had a series of 1, 2, 3 etc. days. Needs to work in a matrix and a diagram as well. It has to be a measure - som not a calculated column.

I found the below solution to the counting of days at SqLBI. It counts the counsecutive days.

But I can't make it count the number of series pr period lenght pr person.  Probably something with counting / summarizing a virtual table - but I just have no luck wit this. Help would be greatly appreciated and paid forward.

The solution for the dax below looks something like this

Cumulative Days off Count =

if ( [hours] =0,

VAR currentdate =

MAX(D_Datetable[Date])

VAR firstdatever=

CALCULATE(MIN( D_Datetable[Date]), REMOVEFILTERS() )

VAR alldateswithFM=

CALCULATETABLE(VALUES( F_Work[shiftend]), REMOVEFILTERS(D_Datetable) )

VAR prevdatewithFM =

MAXX(

FILTER(alldateswithFM, F_work[shiftend] <= currentdate) ,

F_work[shiftend]

)

VAR prevdate=

COALESCE(prevdatewithFM, firstdatever)

VAR result =

INt (currentdate-prevdate )

return

result, 0)

BR

Kasper

[REGHnoob]

So that was not as easy as I thought - but at least I think it works as an example now.

Agian thank you for the interest. Excited to see if anyone has some ideas thats aplicable.

I think opening the file gives the best idea as to whats the issue, but below there is an image of both the count of days (T1)  and the solution (T3)

link to file from google drive 

[AlexisOlson]

I started tinkering with this by calculating a summary table of persons and dates.

Summary = 
FILTER (
    ADDCOLUMNS (
        SUMMARIZECOLUMNS (
            Person[Person_ID],
            D_Datotabel[Dato],
            "Hours", CALCULATE ( SUM ( data[hours] ) ) + 0
        ),
        "ConsecuctiveDays",
            VAR FirstShift = CALCULATE ( MIN ( data[Shift_start] ), REMOVEFILTERS ( D_Datotabel ) )
            VAR DateRange = DATESBETWEEN ( D_Datotabel[Dato], FirstShift, D_Datotabel[Dato] - 1 )
            VAR AllShifts =
                CALCULATETABLE (
                    VALUES ( data[Shift_start] ),
                    ALL ( D_Datotabel[Dato] ),
                    data[hours] > 0
                )
            VAR LastDayOff = MAXX ( EXCEPT ( DateRange, AllShifts ), D_Datotabel[Dato] )
            VAR RunStart = COALESCE ( LastDayOff, FirstShift - 1 )
            RETURN
                IF ( [Hours] > 0, D_Datotabel[Dato] - RunStart )
    ),
    [ConsecuctiveDays] > 0
)

You can turn the ConsecutiveDays part into a measure but there's still some work to be done. I'm attaching a .pbix from where I left off.

[REGHnoob]

Thanks a bunch for your efforts.

Looking forward to test-  fully booked for meetings all day plus 3 deadlines (seriously what do they expect) so might be a stretch if I manage today.

Only thing is that so far when I have work in the table with adding columns it crahshes - I figured the dataset was too large as it has somewhere between 15 and 25 mill lrows and a few more columns than was included in the sample file. 

But still excited too try it out.

[AlexisOlson]

With a dataset that big, you might want to do some pre-aggregation at the query stage if possible.

[REGHnoob]

I 'll try to upload a sample set later today. My motherboard died so I am on a temp machine trying to recreate studd - including this. Thanks for the interest. br kasper

[Anonymous]

Hi @REGHnoob ,

I'm not quite sure if I understand your needs accurately.
Sample data without sensitive information and expected output would help tremendously.
Please see this post regarding: 

How to get your question answered quickly 

How-to-provide-sample-data-in-the-Power-BI-Forum 

Hope it helps,

Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

This sounds challenging. Can you specify what the final result should look like? I think I get the general idea but how you plan to show it in a visual can make a significant difference.