Forum Discussion
IF Statement for a column not working
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/
Most Valuable ProfessionalHi 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
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