Finding the End Date of multiple Registers

[GuillemXII]

Hi everyone, 

I've the following table with this 4 columns: 

Each row represent a day in which a certain worker ("Worker Code") didn't go to work. In the case for example of three rows marked in red, they all belong to a single absence which lasted for 3 days (29, 30 and 31 of May). 

In the fourth column ("Check") with the help of another user, I achieved to mark the row in which a certain absence begun: "1" indicates new absence, "0" indicates continuing absence. 

Check = 
VAR CurrWorker = 'Sheet (2)'[Worker Code]
VAR CurrDate = 'Sheet (2)'[Date]
VAR AbsType = 'Sheet (2)'[Type Absence]
RETURN
IF ( 
    ISBLANK (
        COUNTROWS(
            FILTER('Sheet (2)'; 
            'Sheet (2)'[Worker Code] = CurrWorker &&
            'Sheet (2)'[Type Absence] = AbsType &&
            'Sheet (2)'[Date] < CurrDate &&
            'Sheet (2)'[Date] >= CurrDate -5
            )
        )
    );1;0)

What I need to do next but I've not been able to, is adding a new column where in case of being a new absence ("Check"=1) it shows the last day of the absence. It should look like that:

In the case of the registers marked in red the last day would be "31/05/2019" while for some others, because of being a single day absence (like the first two rows), the date of start and end should be the exact same. 

I tried this code reproducing the idea of the "Check" column: 

Days = 
VAR CurrWorker = 'Sheet (2)'[Worker Code]
VAR CurrDate = 'Sheet (2)'[Date]
VAR AbsType = 'Sheet (2)'[Type Absence]
RETURN
IF ( 
    'Sheet (2)'[Check] = 1 &&
        ISBLANK (
            COUNTROWS(
                FILTER('Sheet (2)'; 
                'Sheet (2)'[Worker Code] = CurrWorker &&
                'Sheet (2)'[Type Absence] = AbsType &&
                'Sheet (2)'[Date] < CurrDate &&
                'Sheet (2)'[Date] >= CurrDate -5
                )
             )
        )
    ;CurrDate; IF(

        'Sheet (2)'[Check] = 1;
        CALCULATE (
            MAX('Sheet (2)'[Date]);
                FILTER('Sheet (2)'; 
                'Sheet (2)'[Worker Code] = CurrWorker &&
                'Sheet (2)'[Type Absence] = AbsType &&
                'Sheet (2)'[Date] < CurrDate &&
                'Sheet (2)'[Date] >= CurrDate -5
                )
            )    
    ;CurrDate))

But it's not working right, it's always copying the date from the current row...

Any tip and advise you can give me to get the right dates will be much appreciated!

Thank you all!

[kranthi82]

Hello @GuillemXII ,

To be quite honest, it is unclear what you want to achieve but if I understand correct, you want a column with the last day of absence in the new column? If yes, then try using group by worker code which should give you latest abscent day in the new column. If not, please explain in a better way so users can understand better.

/Kranthi

[GuillemXII]

Hi @kranthi82 , 

Thanks for your comment. I invested so much time "contextualizing" my data that I ended up not explaining enough my issue...

I've updated the original post with some more info about it.

And yes, your are right, I want a column with the last day of that same absence in the new column. However, if I just group by worker, I'll get errors, because a worker may have multiple abscences through the year which are not directly related (ex: sick leave and delays)...