topic Count people based on condition across multiple dates in DAX Commands and Tips

Count people based on condition across multiple dates

EWBWEBB — Wed, 26 Feb 2025 14:22:25 GMT

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

Re: Count people based on condition across multiple dates

Anonymous — Thu, 27 Feb 2025 04:02:00 GMT

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.

Re: Count people based on condition across multiple dates

EWBWEBB — Thu, 27 Feb 2025 18:30:58 GMT

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.