cumulative sum based on three columns

[Anonymous]

Hello everyone,

         Below is my sample data.

ITEM        CATEGORY    DATE              AMOUNT        RUNNING TOTAL

1              c1                 1/1/2020             100                 100

1              c1                 2/1/2020             150                 250

1              c2                 1/1/2020             200                 200 

1              c3                 2/1/2020             150                 150

2              c1                 2/1/2020             200                 200

2              c2                 1/1/2020             250                 250

2              c3                 1/2/2020             100                 100

2              c3                 2/2/2020             150                 250

The last calculated column named running total is what my requirement. I need to create cumulative amount column based on 3 columns(item,category,date). First i need to filter item column and then category, to calculate the sum of amount according to 'table1'[date]<=EARLIER('table1'[date]) in ascending order. Most import thing is my report page will only have date slicer, item and category slicer will not be presented. My visual is a table visual.

Thanks in advance....

[amitchandak · Accepted solution]

@Anonymous 

Try as column

cumm column= sumx(filter(Table, [ITEM] = earlier([ITEM]) && [CATEGORY] = earlier([CATEGORY]) && [DATE] <= earlier([DATE]) ),[AMOUNT])

As measure

cumm measure= sumx(filter(allselected(Table), [ITEM] = max([ITEM]) && [CATEGORY] = max([CATEGORY]) && [DATE] <= max([DATE]) ),[AMOUNT])

[Anonymous]

@amitchandak  @Fowmy 

thanks to both you,

All your dax expressions are working fine.

[Fowmy · Accepted solution]

@Anonymous 

Add as a new Column:

Cumm = 
var i = [ITEM ]
var cat = [CATEGORY ]
var _date = [DATE ] 
return
CALCULATE(
    SUM('Table'[AMOUNT ]),
    'Table'[ITEM ] = i,
    'Table'[CATEGORY ] = cat,
    'Table'[DATE ] <= _date,
    ALL('Table')
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[amitchandak · Accepted solution]

@Anonymous 

Try as column

cumm column= sumx(filter(Table, [ITEM] = earlier([ITEM]) && [CATEGORY] = earlier([CATEGORY]) && [DATE] <= earlier([DATE]) ),[AMOUNT])

As measure

cumm measure= sumx(filter(allselected(Table), [ITEM] = max([ITEM]) && [CATEGORY] = max([CATEGORY]) && [DATE] <= max([DATE]) ),[AMOUNT])