Forum Discussion

arad33's avatar
arad33
Frequent Visitor
3 years ago

Find End Date from Same Column and Count Records

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 D...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
arad33
Frequent Visitor
3 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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