Continuous days calculation

[ssray]

I am very new to PowerBI and I have a scenario where only certain employees are given access to a specific area. If another employee without access rights swipes his/her card, an error is generated as "Swipe access violation". I want to check if any specific employee (without access rights) has been swiping his/her card continuously for 3 days or more.

List of Data fields - Employee ID, Swipe Date & Time and Error Description

I have created a table in PowerBI with Employee ID and Date in Row field and and a measure.

Measure = CALCULATE([Error Count],DATEADD('Calendar'[Date],-3,DAY))

The above measure is only showing me the number of errors which has happened 3 days before but not the summation of the errors over the last 3 days.

Request help on this issue.

[Eric_Zhang · Accepted solution]

---

@ssray wrote:

I am very new to PowerBI and I have a scenario where only certain employees are given access to a specific area. If another employee without access rights swipes his/her card, an error is generated as "Swipe access violation". I want to check if any specific employee (without access rights) has been swiping his/her card continuously for 3 days or more.

List of Data fields - Employee ID, Swipe Date & Time and Error Description

I have created a table in PowerBI with Employee ID and Date in Row field and and a measure.

Measure = CALCULATE([Error Count],DATEADD('Calendar'[Date],-3,DAY))

The above measure is only showing me the number of errors which has happened 3 days before but not the summation of the errors over the last 3 days.

Request help on this issue.

---

@ssray

Based on my understanding, you could try a measure as below.

The logic is,

1. Get the distinct TOP 3 days error records.
2. In those TOP 3 days, if the MAX day is the same with MAX day of all records of an employee, and MAX day and Min day have 2 days difference, and there're 3 records in the TOP 3 days records, then it indicates the user violate access over last 3 days. 

isContinuous3daysViolateAccessOverLast3days = 
VAR Overlast3ErrorHistory =
    TOPN (
        3,
        DISTINCT (
            SELECTCOLUMNS (
                FILTER ( yourTable, yourTable[Error Description] = "Swipe access violation" ),
                "Employee ID", yourTable[Employee ID],
                "Swipe date", yourTable[Swipe Date & Time]
            )
        ),
        [Swipe date], DESC
    )
RETURN
    IF (
        COUNTROWS ( Overlast3ErrorHistory ) = 3
            && MAXX ( Overlast3ErrorHistory, [Swipe date] )
                = MAX ( yourTable[Swipe Date & Time] )
            && MAXX ( Overlast3ErrorHistory, [Swipe date] )
                - MINX ( Overlast3ErrorHistory, [Swipe date] )
                = 2,
        "Yes",
        "No"
    )

See the attached pbix file. If it is not your case, please provide some sample data and expected output. Even better, you can upload the pbix file. Please upload the pbix file to Onedrive or any web storage and share the link. Do remember to mask sensitive data before uploading.