Calculating bradford factor

[Anonymous]

Hello everyone,

I am trying to calculate the bradford factor, so I have an idea about the absenteeism. For those of you unaware the Bradford factor, it is a formula used to calculate a figure based on absenteeism 

B= S2 x D

• S is the total number of separate absences by an individual
• D is the total number of days of absence of that individual
• B is the Bradford Factor score

My data is as follows:

• Column for name (this means that a lot of the same names return over a year, because of multiple absent records)
• Column for absent start date
• Column for absent end date
• Measured column to calculate days per row with the formula: Dates absent = DATEDIFF(Verzuimgegevens[Begin];Verzuimgegevens[Einde];DAY)

This means that I have got the D to fill in the formula as a column. But I need help with the rest of the formula.

How can one calculate the S (total number of separate absences by an individual) from a column that has names that sometimes return a few times? I tried COUNTDISTINCT, but that didn't give me what I want.

How can one then make the formula into a measure? I have no idea how to do ^2 and * between a column and measure.

Sample data: https://www21.zippyshare.com/v/meubQlxf/file.html

Does anyone have a smarter idea to do this, or can anyone help me?

Thank you for your time and attention.

Best regards,

AliBI

[v-yuta-msft · Accepted solution]

@Anonymous ,

To achieve your requirement, create measures below:

S = CALCULATE(COUNT(Blad1[Naam]), ALLEXCEPT(Blad1, Blad1[Naam]))

B = [S] * MAX('Blad1'[Dates absent])

  

Community Support Team _ Jimmy Tao

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

[Anonymous]

@v-yuta-msft 

Thank you so much for your help. I managed to make it! However, I still have a question which I wished I talked about in my opening post. I am wondering if you have a smart way to handle this problem.

In my sample data (updated version here: https://www2.zippyshare.com/v/t0yRWpV5/file.html) I have the same names and amount of people. However, while there are 8 entries for the name 'Martin' (just like 'S' your table), 4 of them have the same begin and end date. I only want to count one of them, and not all 4 since they are identical. This makes the total amount of S 5, and not 8. Which also makes the name appear only one time, instead for every entry.

I have multiple date entries a lot in my real data, and I am wondering if we can change the measure to skip begin and end dates if they have already mentioned with a name.

Maybe this is some advanced stuff (maybe only for me), but do you (or anyone else) have an idea how I can manage this in Power BI?

Thank you for your time and effort.

Best regards,

[v-yuta-msft]

@Anonymous ,

Have you solved your issue by now? If you have, could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.

Regards,

Jimmy Tao

[v-yuta-msft]

@Anonymous ,

---

@Anonymous wrote:

@v-yuta-msft 

Thank you so much for your help. I managed to make it! However, I still have a question which I wished I talked about in my opening post. I am wondering if you have a smart way to handle this problem.

In my sample data (updated version here: https://www2.zippyshare.com/v/t0yRWpV5/file.html) I have the same names and amount of people. However, while there are 8 entries for the name 'Martin' (just like 'S' your table), 4 of them have the same begin and end date. I only want to count one of them, and not all 4 since they are identical. This makes the total amount of S 5, and not 8. Which also makes the name appear only one time, instead for every entry.

I have multiple date entries a lot in my real data, and I am wondering if we can change the measure to skip begin and end dates if they have already mentioned with a name.

Maybe this is some advanced stuff (maybe only for me), but do you (or anyone else) have an idea how I can manage this in Power BI?

Thank you for your time and effort.

Best regards,

---

Could you share the sample file via onedrive and clarify more details about your expected result?

Community Support Team _ Jimmy Tao

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