Forum Discussion

Honne2021's avatar
Honne2021
Icon for Helper II rankHelper II
4 years ago
Solved

Grouping continuous working dates by name or ID

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 l...
General Comment
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
    Honne2021
    Icon for Helper II rankHelper II
    4 years ago

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

    Employee BJan  05 2022Headache
    Employee BAug 10 2022Headache


    The result should still be the same and not:

    Employee BJan 05 2022Aug 10 2022Headache


    Because the dates are not continuous. ğŸ˜¢


    • ryan_mayu's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      4 years ago

      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's avatar
        Honne2021
        Icon for Helper II rankHelper II
        4 years ago

        appreciate the help! I took note of your formulas ğŸ˜Š