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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
REGHnoob
Frequent Visitor

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

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
consecutive.png
 
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
7 REPLIES 7
REGHnoob
Frequent Visitor

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 

 

2021-12-01 00_19_48-count of series of cumulative days of - Power BI Desktop (maj 2021).png

 

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.

 

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.

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

REGHnoob
Frequent Visitor

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

v-xiaoyan-msft
Community Support
Community Support

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
Super User
Super User

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.