Count people based on condition across multiple dates

[EWBWEBB]

Hi There

I am looking to count the number of people who became long term sick in a period and remained sick at the end of the period.

We work in 4 week period not months so I have to use a Period column for MIN/MAX date ranges.

What I am trying to calcualte is:
The count of people (by PersonNumber) that reached 28 consecutive days sick within a period and remained sick at the end of the period.

I've attached a sample file for one individual and the anticipated behaviour would be:
Period 1 - not counted
Period 2 - counted
Period 3 not counted

I'm only interested in the Paycode "Sick" so could filter the table by that initially.

Reasoning:

Period one - consecutive days sick do not reach 28
Period two - On the first day of the period (29/01/24) consecutive sick days was less than 28, on 05/02/24 they reached 28 consecutive days sick, on the last day of the period (25/02/24) they remained sick.
Period three - started the period with more than 28 conseuctive days sick, was also not sick on the last day of the period (31/03/24).

There will be multiple people in the list so I want something akin to a partition by SQL function I think.

I would like to do this using a measure not a calculated column as the actual data set is quite large. 

Sample File 

[Anonymous]

Hi @EWBWEBB ,

Please crate a measure as follows:

CountLongTermSick = 
SUMX(
    VALUES('Table'[Period]),
    VAR _sickdays = CALCULATE(COUNTROWS('Table'), FILTER(ALLEXCEPT('Table', 'Table'[Period], 'Table'[PersonNumber]), 'Table'[Paycode] = "Sick"))
    VAR _maxdate = CALCULATE(MAX('Table'[Date]), ALLEXCEPT('Table', 'Table'[Period], 'Table'[PersonNumber]))
    VAR _SickAtEndOfPeriod = IF(CALCULATE(MAX('Table'[Paycode]), FILTER(ALLEXCEPT('Table', 'Table'[Period], 'Table'[PersonNumber]), 'Table'[Date] = _maxdate)) = "Sick", 1, 0)
    VAR _count = IF(_sickdays >= 28 && _SickAtEndOfPeriod = 1, CALCULATE(DISTINCTCOUNT('Table'[PersonNumber]), ALLEXCEPT('Table', 'Table'[Period])), 0)
    RETURN _count
)

Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[EWBWEBB]

@Anonymous - thank you so much.
I understand the log there which is really helpful however I've got stuck and should have mentioned this in the original post.

The dates and periods are held in a Dim_Date table joined to the Fact_AttendanceTable on date as one to many.

The Dim_Date table is a row per date with coresponding Period
Fact attendance is a row per person per day - as in the attached file.