cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## How 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?

Regards, Frank

1 ACCEPTED SOLUTION
Community Support

Based on your description, you can create some measures as follows.

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)returnx1+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.

3 REPLIES 3
Community Support

Based on your description, you can create some measures as follows.

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)returnx1+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.

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

Helper IV

Hi Amitchandak,

If i generate your measure or calculated colums, i get only results with value 1. Do you have an example for me?

Regards, Frank