Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Running count by product

Hi all,

I would like to achieve a running count, reset by product, as such (Count column):

ProductCountDate
A110/16/2020
A29/25/2020
A310/2/2020
B18/31/2020
B29/24/2020
C19/23/2020
D110/5/2020
D29/7/2020
D310/8/2020
D410/4/2020
D59/9/2020
D69/28/2020

 

It needs to be grouped by product. It also has to be dynamic when filters are applied to the table, so probably a calculated column or measure and not hardcoded. May I know which formula to use? 

Thank you.

Need Help
  • nandukrishnavs's avatar
    nandukrishnavs
    5 years ago

    Anonymous 

     

    Try this DAX measure

    RunningTotal = 
VAR _date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _product =
    SELECTEDVALUE ( 'Table'[Product] )
VAR _runningTotal =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] <= _date
                && 'Table'[Product] = _product
        ),
        'Table'[Count]
    )
RETURN
    _runningTotal

     

     

     

2 Replies

  • nandukrishnavs's avatar
    nandukrishnavs
    Community Champion
    5 years ago

    Anonymous 

     

    Try this DAX measure

    RunningTotal = 
VAR _date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _product =
    SELECTEDVALUE ( 'Table'[Product] )
VAR _runningTotal =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] <= _date
                && 'Table'[Product] = _product
        ),
        'Table'[Count]
    )
RETURN
    _runningTotal

     

     

     

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try a measure like

     

    calculate( sum(Table[Count]), filter( Table,[Date] <=Max(Table[Date]) && [Product] =Max(Table[Product])))