How to present frequent cases

[frankhofmans]

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

[v-yuaj-msft · Accepted solution]

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+x2

Result:

• 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.

[amitchandak]

@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))

[frankhofmans]

Hi 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