Counting consecutive status by data and employee and status

[swong89]

Hello all.

I have a dataset from a daily survey on attendance.  Some of the fields are date, name of the employee, and status, and they can be entered in any order.  The status is a Yes/No toggle on PowerApps (aka True/False in PowerBI), letting us know if an employee has been routed to another assignment.  An employee may be "pulled"/re-assigned as needed.

Output: We are trying to see if we can count the number of days an employee was pulled consecutively.  The counter/flag would start counting each day until the "pulled" status drops off.  The counter restarts again with more than 1 consecutive re-assignment.

All three fields are normalized in one SharePoint list.  The size is up to 975 entries a day.

I tried writing a code, but it seems not doable on PowerBI as a running report.

[v-henryk-mstf]

Hi @swong89 ,

Try the formula like below:

Column =
RANKX (
    FILTER ( 'Table', 'Table'[Employee] = EARLIER ( 'Table'[Employee] ) ),
    'Table'[Date],
    ,
    ASC,
    DENSE
)

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[swong89]

@v-henryk-mstf 

Sorry.  I just got back to this project.   I tried your formula, but it does not seem to filter the "Pulled" column filter.  The Pulled column has values of "0" and "1", with "1" as being true.  How do I include the "1" into the rankx formula?

[swong89]

Hi @v-henryk-mstf .

I've included some data below based on the data we have collected so far.  The data is already filtered for the Pulled indicator (Column 5).  Every time the employee has a pulled indicator =1 means they were re-assigned.   Whenever that is triggered, I want to start counting the number of times an employee is consecutively by date with the 1 (last column).  The counter would restart when that condition no longer exists.

Date	Employee	Tour	Job Number	Pulled?	Result: Counter
1/22/2022	Person 1	NT	NC0006	1	1
1/17/2022	Person 2	NT	NC0006	1	1
1/18/2022	Person 2	NT	NC0006	1	2
1/21/2022	Person 2	NT	NC0006	1	3
1/19/2022	Person 3	AM	NC0013	1	1
1/21/2022	Person 4	AM	NC0015	1	1
1/16/2022	Person 5	AM	NC0020	1	1
1/23/2022	Person 6	AM	NC0023	1	1
1/17/2022	Person 7	NT	NC0025	1	1
1/23/2022	Person 7	NT	NC0025	1	2
1/17/2022	Person 8	PM	NC0029A	1	1
1/22/2022	Person 9	PM	NC0029A	1	1
1/18/2022	Person 10	PM	NC0032	1	1
1/19/2022	Person 10	PM	NC0032	1	2
1/20/2022	Person 10	PM	NC0032	1	3
1/21/2022	Person 10	PM	NC0032	1	4
1/22/2022	Person 10	PM	NC0032	1	5

Screenshot of the data exported into Excel

[v-henryk-mstf]

Hi @swong89 ,

Based on your description, I don't seem to understand much about the needs you describe in this paragraph.

       

Could you provide the relevant test data and screenshots of the expected results so that I can further answer for you? Also you can ask for help in the powapp community or have a better solution.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 

Looking forward to your feedback.

Best Regards,
Henry