Find End Date from Same Column and Count Records

[arad33]

I'm working on an absence report with the below data set and would like to find the below

First and Last Date of the sickness period so in the first set it would be First date = 30/03/2022 Last Date = 08/04/2022 and the Days in that period would be 8.

I'm guessing once I can set the dates the counts would be a pretty simple DATEDIFF but I just can't figure how to extract the Start & End Date based on the change in Status?

[arad33]

Thank you for the response once again and its now even closer its now just that it is returning the count on the first and last day of the sickness period so in the first part of your example I am getting a count of 8 on 03/30/2022 and on 04/08/2022.  Ideally I just want one return on the last date (04/08/2022) in this instance.

[v-yiruan-msft]

Hi @arad33 ,

If I understand correctly, you would like to get the number of sick date per period. If yes, you can create a measure as below to get it:

Count of sick days =
VAR _seldate =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _empid =
    SELECTEDVALUE ( 'Table'[Employee ID] )
VAR _status =
    SELECTEDVALUE ( 'Table'[Status] )
VAR _preworkdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Status] = "Worked"
                && 'Table'[Date] < _seldate
        )
    )
VAR _nextworkdate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Status] = "Worked"
                && 'Table'[Date] > _seldate
        )
    )
VAR _minsickdate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Status] = "Sick"
                && 'Table'[Date] <= _nextworkdate
                && 'Table'[Date] > _preworkdate
        )
    )
VAR _maxsickdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Status] = "Sick"
                && 'Table'[Date] <= _nextworkdate
                && 'Table'[Date] > _preworkdate
        )
    )
RETURN
    IF (
        _status = "Sick",
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[Date] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Date] >= _minsickdate
                    && 'Table'[Date] <= _maxsickdate
            )
        ),
        BLANK ()
    )

Best Regards

[arad33]

Hi 

Thank you for your reply this is certainly as close as I have ever been and it is returning the data but I now can't get it to return a single line for each period. I have the date linked to a date table but when I use any of those it doesn't work and in the raw table its returning the value multiple times.  For example this user has 44 days total but obviously its returned 33, 33 times, 2 twice and 4, 4 times.  

If I do earliest date I get this

If I link to month in the date table I get this

Ideally if I could get first date or last date of sickness with the number count that would be great, If I could get first and last with the count that would be dream world.

[v-yiruan-msft]

Hi  @arad33 ,

I updated my sample pbix file(see the attachment), please check if that is what you want. You can update the formula of measure as below to get it:

Count of sick days =
VAR _seldate =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _empid =
    SELECTEDVALUE ( 'Table'[Employee ID] )
VAR _status =
    SELECTEDVALUE ( 'Table'[Status] )
VAR _preworkdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Status] = "Worked"
                && 'Table'[Date] < _seldate
        )
    )
VAR _nextworkdate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Status] = "Worked"
                && 'Table'[Date] > _seldate
        )
    )
VAR _minsickdate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Status] = "Sick"
                && 'Table'[Date] <= _nextworkdate
                && 'Table'[Date] > _preworkdate
        )
    )
VAR _maxsickdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Status] = "Sick"
                && 'Table'[Date] <= _nextworkdate
                && 'Table'[Date] > _preworkdate
        )
    )
RETURN
    IF (
        _status = "Sick"
            && _seldate IN { _minsickdate, _maxsickdate },
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[Date] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Date] >= _minsickdate
                    && 'Table'[Date] <= _maxsickdate
            )
        ),
        BLANK ()
    )

Best Regards