Grouping continuous working dates by name or ID

[Honne2021]

Hi, I had been looking around for a solution to this but I couldnt find one. I usually see the opposite of my problem!
I need to write it through M code or through Power Query. 

My report looks like this:

Employee	Date of Leave	Reason
Mike	Sep 1 2022	Fever
Mike	Sep 2 2022	Fever
Mike	Sep 5 2022	Fever
Mike	Sep 6 2022	Fever
Mike	Dec 1 2020	Headache
Mike	Dec 30 2020	Headache

I want the result to look like this:

Employee	First Day of Leave	Last Day of Leave	Reason
Mike	Sep 1 2022	Sep 6 2022	Fever
Mike	Dec 1 2020	Dec 1 2020	Headache
Mike	Dec 30 2020	Dec 30 2020	Headache

Note that September 1 until 6 should be considered as continuous dates (because September 4 and 5 are weekends).

If it is too complicated for Power Query, is there a way I can do it as a table (actual table, not virtual)/sheet in dax?

Thank you

[Anonymous · Accepted solution]

Hi @Honne2021 ,

I have created a simple sample, please reer to it to see if it helps you.

Create 2 columns.

Column first day of leave =
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[Date of Leave] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[month] = EARLIER ( 'Table'[month] )
                && 'Table'[year] = EARLIER ( 'Table'[year] )
                && 'Table'[Reason] = EARLIER ( 'Table'[Reason] )
                && 'Table'[Employee ] = EARLIER ( 'Table'[Employee ] )
        )
    )
VAR _mindate =
    CALCULATE (
        MIN ( 'Table'[Date of Leave] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[month] = EARLIER ( 'Table'[month] )
                && 'Table'[year] = EARLIER ( 'Table'[year] )
                && 'Table'[Reason] = EARLIER ( 'Table'[Reason] )
                && 'Table'[Employee ] = EARLIER ( 'Table'[Employee ] )
        )
    )
VAR _datebetween =
    DATEDIFF ( _mindate, _maxdate, DAY )
RETURN
    IF ( _datebetween <= 7, _mindate, ( 'Table'[Date of Leave] ) )

Column last day of leave =
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[Reason] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[month] = EARLIER ( 'Table'[month] )
                && 'Table'[year] = EARLIER ( 'Table'[year] )
                && 'Table'[Reason] = EARLIER ( 'Table'[Reason] )
                && 'Table'[Employee ] = EARLIER ( 'Table'[Employee ] )
        )
    )
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[Date of Leave] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[month] = EARLIER ( 'Table'[month] )
                && 'Table'[year] = EARLIER ( 'Table'[year] )
                && 'Table'[Reason] = EARLIER ( 'Table'[Reason] )
                && 'Table'[Employee ] = EARLIER ( 'Table'[Employee ] )
        )
    )
VAR _mindate =
    CALCULATE (
        MIN ( 'Table'[Date of Leave] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[month] = EARLIER ( 'Table'[month] )
                && 'Table'[year] = EARLIER ( 'Table'[year] )
                && 'Table'[Reason] = EARLIER ( 'Table'[Reason] )
                && 'Table'[Employee ] = EARLIER ( 'Table'[Employee ] )
        )
    )
VAR _datebetween =
    DATEDIFF ( _mindate, _maxdate, DAY )
RETURN
    IF ( _datebetween <= 7, _maxdate, ( 'Table'[Date of Leave] ) )

If I have misunderstood your meaning, please provide mroe details with your desired output and pbix file without privacy information.

Best Regards

Community Support Team _ Polly

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

[Honne2021]

Thank you for the help. I just had to re-write the code a little bit but it worked, overall! Thank you again!

[ryan_mayu]

@Honne2021 

maybe you can try group by in PQ

[Honne2021]

@ryan_mayu . Thank you for the swift response. It partially worked but I realised I have data that looks like this too:

Employee B	Jan  05 2022	Headache
Employee B	Aug 10 2022	Headache

The result should still be the same and not:

Employee B

Jan 05 2022

Aug 10 2022

Headache

Because the dates are not continuous. 😢

[ryan_mayu]

@Honne2021 

maybe you can try to use DAX to create a table

Table 2 = 
VAR tbl=ADDCOLUMNS('Table',"last",maxx(FILTER('Table','Table'[Employee ]=EARLIER('Table'[Employee ])&&'Table'[Reason]=EARLIER('Table'[Reason])&&'Table'[Date of Leave]<EARLIER('Table'[Date of Leave])),'Table'[Date of Leave]),"next",minx(FILTER('Table','Table'[Employee ]=EARLIER('Table'[Employee ])&&'Table'[Reason]=EARLIER('Table'[Reason])&&'Table'[Date of Leave]>EARLIER('Table'[Date of Leave])),'Table'[Date of Leave]))
VAR tbl2=ADDCOLUMNS(tbl,"last2",if('Table'[Date of Leave] -[last]=1 || (WEEKDAY('Table'[Date of Leave])=2 && 'Table'[Date of Leave]-[last]=3),[last],blank()),"next2",if([next]-'Table'[Date of Leave]=1 || (WEEKDAY('Table'[Date of Leave])=6 && [next]-'Table'[Date of Leave]=3),[next],blank()))
VAR tbl3=ADDCOLUMNS(tbl2,"scope",if(ISBLANK([last2])&&ISBLANK([next2]),"N","Y"))
VAR tbl4=FILTER(tbl3,(ISBLANK([last2])||ISBLANK([next2]))&&[scope]="Y")
VAr tbl5=ADDCOLUMNS(tbl4,"Startdate",if(ISBLANK([last2]),'Table'[Date of Leave],maxx(FILTER(tbl4,'Table'[Date of Leave]<EARLIER('Table'[Date of Leave])),'Table'[Date of Leave])),"enddate",if(ISBLANK([next2]),'Table'[Date of Leave],minx(FILTER(tbl4,'Table'[Date of Leave]>EARLIER('Table'[Date of Leave])),'Table'[Date of Leave])))
var tbl6=SUMMARIZE(tbl5,[Employee ],[Startdate],[enddate],[Reason])
var tbl7=FILTER(tbl3,[scope]="N")
VAR tbl8=SELECTCOLUMNS(tbl7,"Employee ",[Employee ],"Startdate",[Date of Leave],"enddate",[Date of Leave],"Reason",[Reason])
return union(tbl6,tbl8)

pls see the attachment below

[Honne2021]

appreciate the help! I took note of your formulas 😊