Check if event happens two days in a row

[ThomasWeppler]

Hi Power BI community

I have a question. Is it possible to check if an event happens two days in a row.

I want to make a meassure that counts how many times an error happens two days in a row. 

I want to look at a table with

registration [Date]: Diffrent dates

registration [user]: an id that identify the user

registration [sick]: 1 or 0 depending on whether or not the user has been sick that day.

Each time a user is sick two or more days in a row I want the measure to be one higher. Is this possible?

All help is greatly appreciated.

[pankajnamekar25]

Hello @ThomasWeppler 

Sick Days in a Row =
VAR CurrentDate = SELECTEDVALUE('registration'[Date])
VAR CurrentUser = SELECTEDVALUE('registration'[user])

VAR PreviousDay =
CALCULATE(
MAX('registration'[Date]),
FILTER(
ALL('registration'),
'registration'[user] = CurrentUser &&
'registration'[Date] < CurrentDate
)
)

VAR SickToday =
CALCULATE(
MAX('registration'[sick]),
FILTER(
ALL('registration'),
'registration'[user] = CurrentUser &&
'registration'[Date] = CurrentDate
)
)

VAR SickYesterday =
CALCULATE(
MAX('registration'[sick]),
FILTER(
ALL('registration'),
'registration'[user] = CurrentUser &&
'registration'[Date] = PreviousDay
)
)

RETURN
IF(
SickToday = 1 && SickYesterday = 1,
1,
0
)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[ThomasWeppler]

Thanks @pankajnamekar25 
Your answer got me very close to a solution. However I still have one problem. A person can registre sick multiple times in a day and than they are counted twice when I add it all togther with sumx. I cannot just divided by two becuase sometimes they only regsitre once a day. Is there a way where I can make sure that I only count each day once? 

I can be with another measure if that is easier than change the former dax code. 
I already use this code to add the days togther.

sum sickdays in a row = SUMX('2530 Registration', [Sick Days in a Row])

[pankajnamekar25 · Accepted solution]

Hello @ThomasWeppler 

Could you please try this

Sick Days in a Row (Distinct) =
VAR CurrentDate = SELECTEDVALUE('registration'[Date])
VAR CurrentUser = SELECTEDVALUE('registration'[user])

VAR PreviousDay =
CALCULATE(
MAX('registration'[Date]),
FILTER(
ALL('registration'),
'registration'[user] = CurrentUser &&
'registration'[Date] < CurrentDate
)
)

VAR SickToday =
CALCULATE(
MAX('registration'[sick]),
FILTER(
ALL('registration'),
'registration'[user] = CurrentUser &&
'registration'[Date] = CurrentDate
)
)

VAR SickYesterday =
CALCULATE(
MAX('registration'[sick]),
FILTER(
ALL('registration'),
'registration'[user] = CurrentUser &&
'registration'[Date] = PreviousDay
)
)

VAR IsDuplicate =
CALCULATE(
COUNTROWS('registration'),
FILTER(
ALL('registration'),
'registration'[user] = CurrentUser &&
'registration'[Date] = CurrentDate
)
) > 1

RETURN
IF(
SickToday = 1 && SickYesterday = 1 && NOT IsDuplicate,
1,
IF(
SickToday = 1 && SickYesterday = 1 && IsDuplicate,
1 / IsDuplicate,
0
)
)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[ThomasWeppler]

@pankajnamekar25 

I had to change the code a tiny amount to.

Sick Days in a Row (Distinct) =
VAR CurrentDate = SELECTEDVALUE('registration'[Date])
VAR CurrentUser = SELECTEDVALUE('registration'[user])

VAR PreviousDay =
CALCULATE(
MAX('registration'[Date]),
FILTER(
ALL('registration'),
'registration'[user] = CurrentUser &&
'registration'[Date] < CurrentDate
)
)

VAR SickToday =
CALCULATE(
MAX('registration'[sick]),
FILTER(
ALL('registration'),
'registration'[user] = CurrentUser &&
'registration'[Date] = CurrentDate
)
)

VAR SickYesterday =
CALCULATE(
MAX('registration'[sick]),
FILTER(
ALL('registration'),
'registration'[user] = CurrentUser &&
'registration'[Date] = PreviousDay
)
)

VAR IsDuplicate =
CALCULATE(
COUNTROWS('registration'),
FILTER(
ALL('registration'),
'registration'[user] = CurrentUser &&
'registration'[Date] = CurrentDate
)
) > 1

RETURN
IF(
SickToday = 1 && SickYesterday = 1 && NOT IsDuplicate,
1,
IF(
SickToday = 1 && SickYesterday = 1 && IsDuplicate,
1 / (IsDuplicate+1),
0
)
)

But it was pretty close so I still accepted your solution.
Thanks a ton for the help. 🙂 

[ThomasWeppler]

Thanks for the quick answer again@pankajnamekar25 

Unfortunately it didn't work.

the sumx has been changed to 

sumx sickdays in a row = SUMX('Registration', [Sick Days in a Row (Distinct)])

and I still get two registrations when two registrations has been made on the same day.