Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Resetting a cummulative counter in calculated column

Hello PBI Experts


I am trying to create a calculated column which will display the running total of a particular Status value by ItemID and Date until the status value changes.  If the status value occurs again I want the running total to reset to 1. 

 

Here is my example data.  The column DesiredOutput is what I want the calculated column to display.  

 

I have been struggling with this for a while so I hope someone can help. Thanks.

 

DateItemIDStatusStatusCounterDesiredOutput
01 Jan 202022222A00
02 Jan 202022222B11
03 Jan 202022222B12
04 Jan 202022222A00
01 Jan 202055555B11
02 Jan 202055555B12
03 Jan 202055555A00
04 Jan 202055555B11
     
Need Help

4 Replies

  • v-eachen-msft's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    You could use EARLIER() function to get previous value.

    At first, create an index column with RANKX() function. You could also create it in the query editor.

    Index =
RANKX (
    FILTER ( 'Table', 'Table'[ItemID] = EARLIER ( 'Table'[ItemID] ) ),
    'Table'[Date],
    ,
    ASC,
    DENSE
)

    Then use IF() to get the result.

    Output =
VAR a =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[StatusCounter], 1 ),
        FILTER (
            'Table',
            'Table'[Index]
                = EARLIER ( 'Table'[Index] ) - 1
                && 'Table'[ItemID] = EARLIER ( 'Table'[ItemID] )
        )
    )
RETURN
    IF ( 'Table'[StatusCounter] = 0, 0, a + 'Table'[StatusCounter] )

    Here is my test file for your reference.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you for your suggestion.

       

      I have tried this approach and whilst the "Output" column resets to ZERO at the correct point the cummulative count in this column never exceeds a value of 2. 

      In my real data the cummulative count in this column may gets as high as 50 before a reset to zero event occurs so the calcuation needs to continue to accumulate beyond 2.

      • v-eachen-msft's avatar
        v-eachen-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous ,

         

        I have edited my test file.

        At first, create an index column in the query editor.

        Then create a "Group" column to sort data.

        Group =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[Index] <= EARLIER ( 'Table'[Index] )
            && 'Table'[Status] = "A"
    )
)

        Based on the "Group", use RANKX() to get result.

        OutPut =
VAR a =
    RANKX (
        FILTER (
            'Table',
            'Table'[Status] = EARLIER ( 'Table'[Status] )
                && 'Table'[Index] <= EARLIER ( 'Table'[Index] )
                && 'Table'[Group] = EARLIER ( 'Table'[Group] )
        ),
        'Table'[Index],
        ,
        ASC,
        DENSE
    )
RETURN
    IF ( 'Table'[StatusCounter] = 0, 0, a )

        Here is the new file for your reference.