Running Counts with Reset based on value of specific column.

[ShrodingerBrain]

Hi Hello,

Seeking your kind assistance.

(Image remove due to Data Security)

I want to learn how to do a Running Counts DAX that will reset the count everytime the text in column "Status" change.

1,2,3,1,2,3,1,2,3,

Been trying, and almost give up now.

Can anyone help teach me? thanks in advance.

[rajendraongole1 · Accepted solution]

Hi @ShrodingerBrain - can you use below calculated column

Calculated column:

RunningCount =

VAR CurrentStatus = Running[Status]

VAR CurrentDate = Running[Date]

RETURN

CALCULATE(

    COUNTROWS(Running),

    FILTER(

        Running,

        Running[Status] = CurrentStatus &&

        Running[Date] <= CurrentDate

    )

)

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Ashish_Mathur]

Hi,

This should ideally be done in Power Query.  Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Anonymous · Accepted solution]

Hi @ShrodingerBrain ,

@rajendraongole1 's workaround is valid.

However, his workaround is based on a sorted date column and a group column. If there's no such columns in your table, then this workaround cannot be implemented.

You can add an index column in power query editor to implement it.

Create a calculated column.

Column = SWITCH(MOD([Index],3),1,1,2,2,0,3)

You can download the attachment for details.

Best Regards,

Stephen Tao

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

[rajendraongole1 · Accepted solution]

Hi @ShrodingerBrain - can you use below calculated column

Calculated column:

RunningCount =

VAR CurrentStatus = Running[Status]

VAR CurrentDate = Running[Date]

RETURN

CALCULATE(

    COUNTROWS(Running),

    FILTER(

        Running,

        Running[Status] = CurrentStatus &&

        Running[Date] <= CurrentDate

    )

)

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!