Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
EWBWEBB
Helper III
Helper III

Count people based on condition across multiple dates

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 

 

2 REPLIES 2
Anonymous
Not applicable

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:

vlinhuizhmsft_0-1740628834556.png

 

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.

@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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.