Find End Date from Same Column and Count Records

Question

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?

anonymous · Answer

Hi&nbsp;arad33&nbsp;,
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"
                &amp;&amp; 'Table'[Date] &lt; _seldate
        )
    )
VAR _nextworkdate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Status] = "Worked"
                &amp;&amp; 'Table'[Date] &gt; _seldate
        )
    )
VAR _minsickdate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Status] = "Sick"
                &amp;&amp; 'Table'[Date] &lt;= _nextworkdate
                &amp;&amp; 'Table'[Date] &gt; _preworkdate
        )
    )
VAR _maxsickdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Status] = "Sick"
                &amp;&amp; 'Table'[Date] &lt;= _nextworkdate
                &amp;&amp; 'Table'[Date] &gt; _preworkdate
        )
    )
RETURN
    IF (
        _status = "Sick",
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[Date] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Date] &gt;= _minsickdate
                    &amp;&amp; 'Table'[Date] &lt;= _maxsickdate
            )
        ),
        BLANK ()
    )

Best Regards

arad33 · Answer

Hi&nbsp;&nbsp;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.&nbsp; For example this user has 44 days total but obviously its returned 33, 33 times, 2 twice and 4, 4 times.&nbsp;&nbsp;&nbsp;If I do earliest date I get thisIf I link to month in the date table I get this&nbsp;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 · Answer

Hi&nbsp;&nbsp;arad33&nbsp;,
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&nbsp;of&nbsp;sick&nbsp;days&nbsp;=VAR&nbsp;_seldate&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;SELECTEDVALUE&nbsp;(&nbsp;'Table'[Date]&nbsp;)VAR&nbsp;_empid&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;SELECTEDVALUE&nbsp;(&nbsp;'Table'[Employee&nbsp;ID]&nbsp;)VAR&nbsp;_status&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;SELECTEDVALUE&nbsp;(&nbsp;'Table'[Status]&nbsp;)VAR&nbsp;_preworkdate&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MAX&nbsp;(&nbsp;'Table'[Date]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED&nbsp;(&nbsp;'Table'&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Table'[Status]&nbsp;=&nbsp;"Worked"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;'Table'[Date]&nbsp;&lt;&nbsp;_seldate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;)VAR&nbsp;_nextworkdate&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MIN&nbsp;(&nbsp;'Table'[Date]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED&nbsp;(&nbsp;'Table'&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Table'[Status]&nbsp;=&nbsp;"Worked"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;'Table'[Date]&nbsp;&gt;&nbsp;_seldate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;)VAR&nbsp;_minsickdate&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MIN&nbsp;(&nbsp;'Table'[Date]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED&nbsp;(&nbsp;'Table'&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Table'[Status]&nbsp;=&nbsp;"Sick"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;'Table'[Date]&nbsp;&lt;=&nbsp;_nextworkdate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;'Table'[Date]&nbsp;&gt;&nbsp;_preworkdate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;)VAR&nbsp;_maxsickdate&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MAX&nbsp;(&nbsp;'Table'[Date]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED&nbsp;(&nbsp;'Table'&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Table'[Status]&nbsp;=&nbsp;"Sick"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;'Table'[Date]&nbsp;&lt;=&nbsp;_nextworkdate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;'Table'[Date]&nbsp;&gt;&nbsp;_preworkdate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;)RETURN&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;_status&nbsp;=&nbsp;"Sick"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;_seldate&nbsp;IN&nbsp;{&nbsp;_minsickdate,&nbsp;_maxsickdate&nbsp;},&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DISTINCTCOUNT&nbsp;(&nbsp;'Table'[Date]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED&nbsp;(&nbsp;'Table'&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Table'[Date]&nbsp;&gt;=&nbsp;_minsickdate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;'Table'[Date]&nbsp;&lt;=&nbsp;_maxsickdate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;BLANK&nbsp;()&nbsp;&nbsp;&nbsp;&nbsp;)

Best Regards

arad33 · Answer

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.&nbsp; Ideally I just want one return on the last date (04/08/2022) in this instance.

Forum Discussion

Find End Date from Same Column and Count Records

4 Replies

Related content

Counting Non-Blank Records

Total Count in Column

How to count blank record and un-blank record using DAX in filter Calculations

Counting nulls

Count Lastest Records Before Selected Date