cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Bradford Score and sick sessions calculations

Hi everyone,

I'm trying to calcualte the Bradford Score for everyone in the company but none of the threads I found online were enough to help me get my head around a way of working it out. My data does not contain sick session, just the work days interval the employees have been sick. In the example below you see that some team members have consecutive weeks of sickness that should be counted as one sick session.

'Sick' table sample below

 Email Begin Date End Date team.member_1@company.co.uk 19/07/2021 23/07/2021 team.member_1@company.co.uk 26/07/2021 30/07/2021 team.member_1@company.co.uk 02/08/2021 06/08/2021 team.member_2@company.co.uk 18/03/2021 18/03/2021 team.member_2@company.co.uk 21/04/2021 21/04/2021 team.member_2@company.co.uk 17/05/2021 19/05/2021 team.member_3@company.co.uk 18/03/2021 19/03/2021 team.member_3@company.co.uk 03/08/2021 06/08/2021 team.member_3@company.co.uk 09/08/2021 13/08/2021 team.member_3@company.co.uk 16/08/2021 20/08/2021

What would be the best approach you'd suggest?

Thanks.

6 REPLIES 6
Community Support

Hi  @mihaiem ,

Here are the steps you can follow：

1. Create a calendar

``Date_Table = CALENDARAUTO()``

2. Create a calculated column in the calendar table.

``week = WEEKDAY('Date_Table'[Date])``
``Tag = IF([week]>5,0,1)``

3. Create a calculated column in the Email_ table.

``````Workday =
CALCULATE(SUM('Date_Table'[Tag]),FILTER(ALL('Date_Table'),'Date_Table'[Date]>='Email_Table'[Begin Date]&&'Date_Table'[Date]<='Email_Table'[End Date]))``````
``````Sum_workday =
CALCULATE(SUM('Email_Table'[Workday]),FILTER(ALL('Email_Table'),'Email_Table'[Email]=EARLIER('Email_Table'[Email])))``````

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Frequent Visitor

Thanks for your reply. I've tweaked a bit the tag column as it was flagging Friday (6) as 0.

``Tag = IF([week]=7,0,if([week]=1,0,1))``

Now I have to combine your solution with the one from @jeroendekk and calcualte the Bradford score, the only issue I have is that there are people submitting multiple hours in same day and that generate a number of 2 sessions or more. I think I have to summarize the whole 'Sick' table into a new one and try to reaply those steps. Thank you both for your answers.

Resolver IV

Hi @mihaiem
I would create a measure something like this.

``````Number of sessions =
SUMX(
Sick,

var user = Sick[Email]
-- gets employee for each row

var begindate = Sick[Begin Date]
--find begindate for each row

var lastenddate =
CALCULATE(
MAX(
Sick[End Date]),
ALLEXCEPT(
Sick,Sick[Email]), Sick[End Date] <= begindate)
-- Calculates previous enddate for each employee (for each row)

var is_new_session =
IF(
AND(WEEKDAY(begindate)=2,begindate - lastenddate <= 3),0,1)

--determines if the begindate is a monday and the previous enddate was on the prev. friday or later
--if true then 0 else 1 (new sessions)

RETURN is_new_session)``````

It think this wil work. But it wont be a fast measure I think (depending on the size of the table).  You could also use this as a calculated column.

Maybe somebody has a better solution.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Best regards,
Jeroen

Frequent Visitor

Thank you for your response. I've tried out your measure but for some entries it does not work as it should. By the looks of it, entires that start and end on the same Monday  it won't be counted. Below I've generated a new test sample.

 Email Begin Date End Date team.member_1@company.co.uk 19/07/2021 23/07/2021 team.member_1@company.co.uk 26/07/2021 30/07/2021 team.member_1@company.co.uk 02/08/2021 06/08/2021 team.member_2@company.co.uk 18/03/2021 18/03/2021 team.member_2@company.co.uk 21/04/2021 21/04/2021 team.member_2@company.co.uk 17/05/2021 19/05/2021 team.member_3@company.co.uk 18/03/2021 19/03/2021 team.member_3@company.co.uk 03/08/2021 06/08/2021 team.member_3@company.co.uk 09/08/2021 13/08/2021 team.member_3@company.co.uk 16/08/2021 20/08/2021 team.member_4@company.co.uk 18/01/2021 18/01/2021 team.member_4@company.co.uk 20/01/2021 20/01/2021 team.member_5@company.co.uk 24/05/2021 24/05/2021

Super User

Hi,

Please clearly show the end result that you are expecting.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Resolver IV

Hi @mihaiem
Right, makes sense. The begindate cannot be the sames as the lastdate. So in the variable lastenddate the operator should be <  not <=
Like this.

``````Number of sessions =
SUMX(
Sick,

var user = Sick[Email]
-- gets employee for each row

var begindate = Sick[Begin Date]
--find begindate for each row

var lastenddate =
CALCULATE(
MAX(
Sick[End Date]),
ALLEXCEPT(
Sick,Sick[Email]), Sick[End Date] < begindate)
-- Calculates previous enddate for each employee (for each row)

var is_new_session =
IF(
AND(WEEKDAY(begindate)=2,begindate - lastenddate <= 3),0,1)

--determines if the begindate is a monday and the previous enddate was on the prev. friday or later
--if true then 0 else 1 (new sessions)

RETURN is_new_session)``````

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors