Forum Discussion

EWBWEBB's avatar
EWBWEBB
Icon for Helper III rankHelper III
1 year ago

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 Pe...
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
EWBWEBB
Icon for Helper III rankHelper III
1 year ago

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.