IF Statement for a column not working

[Anonymous]

Hi, I am currently trying to create a column with an IF statement that returns back a 1 if true or 0 if false.

The table shows every time a user has taken a sick day in their period of working and each time is registered as a separate event, so for example, if someone called in sick for 2 days in a row, it still shows as two separate events. Also, this means the User ID comes up multiple times.

What the filter needs to do is to figure out whether a user has had 9 or more absences in a 4 month period, and if they have then for their ID mak down as 1 (true), if not then 0 (false).

There is a separate column for User ID, their start date, their end date, and a sick column then marks down as 1 or 0.5 per sickness leave.

It might be better to add up the sick days first in a column but I was not sure because it only matter if it is 9 or more absences in 4 months.

I've tried a lot of methods but I am not familiar with DAX yet and am unsure.

Would someone be able to help please?

Below is what I was working opn but it does not work:

if(([SicknessDays]>8), (datediff([StartDate], [EndDate], [60])), true(), false())

[tackytechtom · Accepted solution]

Hi @Anonymous ,

Thanks for the data! I think I got to a solution:

I did it in two steps but both steps are already incorporated in the measure below. First, I created the column TomsRolling4Column which returns the 4 month window for each User ID (see picture above) This equals everything in the FILTER statement below (in green). Afterwards, we only need to distinct count all User IDs which have a number >= 9 in the new column (blue below). 

TomsRolling4Measure = 
CALCULATE ( 
    DISTINCTCOUNT ( Table[User ID] ),
    FILTER (
        Table,
        CALCULATE (
            SUM (Table[Sickness Amount (Days)] ),
            ALLEXCEPT ( Table, Table[User ID] ),
            DATESINPERIOD(
                Table[Sickness Date],
                Table[Sickness Date],
                -4, MONTH
            )
        ) >= 9 
    )
)

Please try it and let me know if it works 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[tackytechtom]

Hi @Anonymous ,

Knowing that this is some pretty sensitive data you are working with, do you still think you could provide some anonymized / mockup sample data as well as the outcome you'd like to achieve? 

Thanks!

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[Anonymous]

Hi @tackytechtom ,

Thank you. Below I have aded a mockup of the data. The last column is the Count which returns a 1 (True) if someone has had 9 absences or more in a 4 month period. I am unsure if this is the best way to do it because all I need to do is have a Count on the Report that shows how many Users have had 9 or more absences in a 4 month period. I then need a way to reset that count live.

User 189 has had 12 absences but only 10 are in a 4 month period of each other so those have a Count of 1 but the other two do not. Also, the Sickness Amount has to add up to 9 or more bearing in mind that some have a count of 0.5 for half days.

Thank you,

Chezz

User ID	Work Start Date	Work End Date	Sickness Date	Sickness Amount (Days)	Count
122	02/02/2015	05/10/2016	11/12/2015	1.0	0
122	02/02/2015	05/10/2016	12/12/2015	1.0	0
122	02/02/2015	05/10/2016	13/12/2015	1.0	0
122	02/02/2015	05/10/2016	03/09/2016	0.5	0
214	01/01/2018	04/06/2018	09/05/2018	1.0	0
214	01/01/2018	04/06/2018	02/06/2018	1.0	0
189	03/05/2019	14/12/2021	03/01/2020	1.0	1
189	03/05/2019	14/12/2021	04/01/2020	1.0	1
189	03/05/2019	14/12/2021	05/01/2020	1.0	1
189	03/05/2019	14/12/2021	06/01/2020	1.0	1
189	03/05/2019	14/12/2021	07/01/2020	1.0	1
189	03/05/2019	14/12/2021	17/02/2020	1.0	1
189	03/05/2019	14/12/2021	18/02/2020	1.0	1
189	03/05/2019	14/12/2021	19/02/2020	1.0	1
189	03/05/2019	14/12/2021	01/03/2020	1.0	1
189	03/05/2019	14/12/2021	02/03/2020	1.0	1
189	03/05/2019	14/12/2021	14/11/2021	1.0	0
189	03/05/2019	14/12/2021	15/11/2021	0.5	0