Forum Discussion
Helper IVHow to present frequent cases
Hi PBI experts,
I have the following problem:
i have a database with sick employees and the sickness date. I want to present the number of sickness cases if:
- a employee is sick 3 times or more in the 6 months before the last sickness date
- a empoloyee is sick 4 times or more in the 12 months before the last sickness date
This is an example of my database:
| Employee ID | Sickness date |
| 110 | 01-12-2019 |
| 123 | 01-12-2020 |
| 135 | 03-01-2020 |
| 147 | 07-01-2020 |
| 178 | 20-02-2020 |
| 159 | 10-01-2020 |
| 200 | 01-03-2020 |
| 201 | 05-03-2020 |
| 267 | 10-03-2020 |
| 123 | 10-04-2020 |
| 159 | 10-05-2020 |
| 178 | 12-05-2020 |
| 110 | 13-05-2020 |
| 110 | 20-05-2020 |
| 159 | 01-06-2020 |
| 123 | 03-06-2020 |
| 200 | 01-07-2020 |
| 200 | 03-08-2020 |
| 178 | 10-08-2020 |
| 178 | 01-09-2020 |
I want 1 graph and 1 table:
Graph:
| Month/Year | 01-2020 | 02-2020 | 03-2020 | 04-2020 | 05-2020 | 06-2020 | 07-2020 | 08-2020 | 09-2020 |
| # of frequent cases | 0 | 0 | 0 | 0 | 2 (2x empl. ID 110) | 2 (empl ID 159 and empl ID 123) | 2 (2x empl. ID 200) | 1 (empl ID 178) | 1 (emp ID 178) |
Table (for example selected periode 06-2020)
| Employee ID | Last sickness date | # of cases in 6 months before latest sickness date | # of cases in 12 months before latest sickness date |
| 123 | 03-06-2020 | 3 | 3 |
| 159 | 01-06-2020 | 3 | 3 |
Can someone help me out with this question?
Thanks in advance!
Regards, Frank
Hi frankhofmans ,
Based on your description, you can create some measures as follows.
Thanks for amitchandak 's advise.
Test table:
rolling 12 months = CALCULATE(COUNT('Table'[Sickness date]),DATESINPERIOD('Table'[Sickness date],MAX('Table'[Sickness date]),-12,MONTH))
rolling 6 months = CALCULATE(COUNT('Table'[Sickness date]),DATESINPERIOD('Table'[Sickness date],MAX('Table'[Sickness date]),-6,MONTH))
Measure 2 =
var x1=IF(CALCULATE(COUNT('Table'[Sickness date]),DATESINPERIOD('Table'[Sickness date],MAX('Table'[Sickness date]),-12,MONTH))>=4,1)
var x2=IF(CALCULATE(COUNT('Table'[Sickness date]),DATESINPERIOD('Table'[Sickness date],MAX('Table'[Sickness date]),-6,MONTH))>=3,1)
return
x1+x2Result:
- Graph:
- Table:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
frankhofmans , Try with help from date table
Rolling 6 = CALCULATE(sum(Table[Sickness date]),DATESINPERIOD('Date'[Date ],MAX(Table[Sickness Date]),-6,MONTH))
Rolling 12 = CALCULATE(sum(Table[Sickness date]),DATESINPERIOD('Date'[Date ],MAX(Table[Sickness Date]),-12,MONTH))
frankhofmansHi Amitchandak,
If i generate your measure or calculated colums, i get only results with value 1. Do you have an example for me?
Thanks in advance,
Regards, Frank
Hi frankhofmans ,
Based on your description, you can create some measures as follows.
Thanks for amitchandak 's advise.
Test table:
rolling 12 months = CALCULATE(COUNT('Table'[Sickness date]),DATESINPERIOD('Table'[Sickness date],MAX('Table'[Sickness date]),-12,MONTH))
rolling 6 months = CALCULATE(COUNT('Table'[Sickness date]),DATESINPERIOD('Table'[Sickness date],MAX('Table'[Sickness date]),-6,MONTH))
Measure 2 =
var x1=IF(CALCULATE(COUNT('Table'[Sickness date]),DATESINPERIOD('Table'[Sickness date],MAX('Table'[Sickness date]),-12,MONTH))>=4,1)
var x2=IF(CALCULATE(COUNT('Table'[Sickness date]),DATESINPERIOD('Table'[Sickness date],MAX('Table'[Sickness date]),-6,MONTH))>=3,1)
return
x1+x2Result:
- Graph:
- Table:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
