The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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.
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.
@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.
User | Count |
---|---|
28 | |
11 | |
8 | |
6 | |
5 |
User | Count |
---|---|
35 | |
14 | |
12 | |
9 | |
7 |