Forum Discussion

TerriAki's avatar
TerriAki
Frequent Visitor
4 years ago
Solved

Consecutive streak into a date range

Hi,     Kinda new here and would really appreciate some help ðŸ™‚   I have a time and attendance table and trying to work out the continues period of sick absence with a value of S, SC or SB for ea...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi TerriAki 
    I was able to double the speed by creating relationships as per below screenshot. I tried on 2.7M rows table and still takes around 50-55 sec. on my machine which is not super-fast. Still too slow and also you have to know that the time increases exponentially with the number of rows and I have no idea how many columns you have. If you have too many columns we need to select only the relevant ones

    Start - End = 
IF (
    'T&A table 2'[Status] IN 'Sick Absence Code',
    VAR CurrentDate = 
        'T&A table 2'[Date]
    VAR EmployeeTable =
        CALCULATETABLE ( 'T&A table 2',  ALLEXCEPT ( 'T&A table 2', 'T&A table 2'[Employee ID] ) )
    VAR OffDaysTable =
        CALCULATETABLE ( 'T&A table 2', ALLEXCEPT ( 'T&A table 2','T&A table 2'[Employee ID] ), 'Sick Absence Code' )
    VAR BreakDaysTable = 
        CALCULATETABLE ( 'T&A table 2', ALLEXCEPT ( 'T&A table 2','T&A table 2'[Employee ID] ), 'Break codes' )
    -- Calculating last day off 
    VAR NexBreaksTable = 
        FILTER ( BreakDaysTable, 'T&A table 2'[Date] >= CurrentDate )
    VAR NextBreakDate =
        MINX ( NexBreaksTable, 'T&A table 2'[Date] )
    VAR NextOffDaysTable =
        FILTER ( OffDaysTable, 'T&A table 2'[Date] < NextBreakDate )
    VAR LastDayOff =
        MAXX ( NextOffDaysTable, 'T&A table 2'[Date] )
    RETURN
        IF (
            CurrentDate = LastDayOff,
            -- Calculating first day off 
            VAR PreviousBreaksTable = 
                FILTER ( BreakDaysTable, 'T&A table 2'[Date] <= CurrentDate )
            VAR PreviousBreakDate =
                MAXX ( PreviousBreaksTable, 'T&A table 2'[Date] )
            VAR PreviousOffDaysTable =
                FILTER ( OffDaysTable, 'T&A table 2'[Date] > PreviousBreakDate )
            VAR FirstDayOff = 
                MINX ( PreviousOffDaysTable, 'T&A table 2'[Date] )
            VAR Result =
                    FirstDayOff & " - " & LastDayOff
            RETURN
                Result
        )
)
TerriAki's avatar
TerriAki
Frequent Visitor
4 years ago

tamerj1 Amazing!  This is very close and is reliably producing the result, is there any way to adapt the code to give the open ended absence a date too?   Also the optimisation would be really appreciated as will be running this on very large dataset.  

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

TerriAki 

The problem is that most probably addapting open end absence would affect the preformance but I wIll gove a try. Meanwhile, plea

 try on your full set of data and let me know how the performance is. 

  • TerriAki's avatar
    TerriAki
    Frequent Visitor
    4 years ago

    I think I can handle the open ended absence by getting Power Query to put in a final row for each Employee with a break code.   Regarding optimisation, it currently isnt running on the full dataset due to running out of memory (I have 16 GB ram and have manually tried increasing Power BI cache settings).   

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Alright

      I have some ideas. Will send you something tomorrow morning

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Hi TerriAki 
      I was able to double the speed by creating relationships as per below screenshot. I tried on 2.7M rows table and still takes around 50-55 sec. on my machine which is not super-fast. Still too slow and also you have to know that the time increases exponentially with the number of rows and I have no idea how many columns you have. If you have too many columns we need to select only the relevant ones

      Start - End = 
IF (
    'T&A table 2'[Status] IN 'Sick Absence Code',
    VAR CurrentDate = 
        'T&A table 2'[Date]
    VAR EmployeeTable =
        CALCULATETABLE ( 'T&A table 2',  ALLEXCEPT ( 'T&A table 2', 'T&A table 2'[Employee ID] ) )
    VAR OffDaysTable =
        CALCULATETABLE ( 'T&A table 2', ALLEXCEPT ( 'T&A table 2','T&A table 2'[Employee ID] ), 'Sick Absence Code' )
    VAR BreakDaysTable = 
        CALCULATETABLE ( 'T&A table 2', ALLEXCEPT ( 'T&A table 2','T&A table 2'[Employee ID] ), 'Break codes' )
    -- Calculating last day off 
    VAR NexBreaksTable = 
        FILTER ( BreakDaysTable, 'T&A table 2'[Date] >= CurrentDate )
    VAR NextBreakDate =
        MINX ( NexBreaksTable, 'T&A table 2'[Date] )
    VAR NextOffDaysTable =
        FILTER ( OffDaysTable, 'T&A table 2'[Date] < NextBreakDate )
    VAR LastDayOff =
        MAXX ( NextOffDaysTable, 'T&A table 2'[Date] )
    RETURN
        IF (
            CurrentDate = LastDayOff,
            -- Calculating first day off 
            VAR PreviousBreaksTable = 
                FILTER ( BreakDaysTable, 'T&A table 2'[Date] <= CurrentDate )
            VAR PreviousBreakDate =
                MAXX ( PreviousBreaksTable, 'T&A table 2'[Date] )
            VAR PreviousOffDaysTable =
                FILTER ( OffDaysTable, 'T&A table 2'[Date] > PreviousBreakDate )
            VAR FirstDayOff = 
                MINX ( PreviousOffDaysTable, 'T&A table 2'[Date] )
            VAR Result =
                    FirstDayOff & " - " & LastDayOff
            RETURN
                Result
        )
)