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

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

Reply
mihaiem
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

 

EmailBegin DateEnd Date
team.member_1@company.co.uk19/07/202123/07/2021
team.member_1@company.co.uk26/07/202130/07/2021
team.member_1@company.co.uk02/08/202106/08/2021
team.member_2@company.co.uk18/03/202118/03/2021
team.member_2@company.co.uk21/04/202121/04/2021
team.member_2@company.co.uk17/05/202119/05/2021
team.member_3@company.co.uk18/03/202119/03/2021
team.member_3@company.co.uk03/08/202106/08/2021
team.member_3@company.co.uk09/08/202113/08/2021
team.member_3@company.co.uk16/08/202120/08/2021

What would be the best approach you'd suggest?

Thanks.

6 REPLIES 6
v-yangliu-msft
Community Support
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)

vyangliumsft_0-1638175586288.png

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:

vyangliumsft_1-1638175586290.png

 

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

Hi @v-yangliu-msft,

 

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.

jeroendekk
Resolver IV
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.
Appreciate your Kudos!!

Best regards,
Jeroen


 

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.

EmailBegin DateEnd Date
team.member_1@company.co.uk19/07/202123/07/2021
team.member_1@company.co.uk26/07/202130/07/2021
team.member_1@company.co.uk02/08/202106/08/2021
team.member_2@company.co.uk18/03/202118/03/2021
team.member_2@company.co.uk21/04/202121/04/2021
team.member_2@company.co.uk17/05/202119/05/2021
team.member_3@company.co.uk18/03/202119/03/2021
team.member_3@company.co.uk03/08/202106/08/2021
team.member_3@company.co.uk09/08/202113/08/2021
team.member_3@company.co.uk16/08/202120/08/2021
team.member_4@company.co.uk18/01/202118/01/2021
team.member_4@company.co.uk20/01/202120/01/2021
team.member_5@company.co.uk24/05/202124/05/2021



Hi,

Please clearly show the end result that you are expecting.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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)



 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.