cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
swong89
Frequent Visitor

Counting consecutive status by data and employee and status

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.

4 REPLIES 4
v-henryk-mstf
Community Support
Community Support

Hi @swong89 ,

 

Try the formula like below:

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

vhenrykmstf_0-1643091625887.png

Best Regards,
Henry


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

@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
Frequent Visitor

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.

 

DateEmployeeTourJob NumberPulled?Result: Counter
1/22/2022Person 1NTNC000611
1/17/2022Person 2NTNC000611
1/18/2022Person 2NTNC000612
1/21/2022Person 2NTNC000613
1/19/2022Person 3AMNC001311
1/21/2022Person 4AMNC001511
1/16/2022Person 5AMNC002011
1/23/2022Person 6AMNC002311
1/17/2022Person 7NTNC002511
1/23/2022Person 7NTNC002512
1/17/2022Person 8PMNC0029A11
1/22/2022Person 9PMNC0029A11
1/18/2022Person 10PMNC003211
1/19/2022Person 10PMNC003212
1/20/2022Person 10PMNC003213
1/21/2022Person 10PMNC003214
1/22/2022Person 10PMNC003215

 

Screenshot of the data exported into Excel

Screenshot 2022-01-24 174320.png

v-henryk-mstf
Community Support
Community Support

Hi @swong89 ,

 

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

       vhenrykmstf_0-1642991397827.png

 

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

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors