Cumulative Running Total by Category or Department

[anuxps]

I need a DAX forumla to get running total by Department and year and month. Here is my data sample. Assuming table name is Expenses and all these column are present in the same table.

Department	Month	Year	Expense	ExpenseDate	Running Total
HR	Jan	2020	1000	1/1/2020	1000
HR	Feb	2020	500	2/1/2020	1500
HR	Mar	2020	300	3/1/2020	1800
MFG	Feb	2020	500	2/1/2020	500
MFG	Mar	2020	600	3/1/2020	1100
Sales	Jan	2020	100	1/1/2020	100
Sales	Feb	2020	200	2/1/2020	300
Sales	Mar	2020	300	3/1/2020	600
Sales	Mar	2020	400	3/1/2020	1000
Sales	Feb	2020	500	2/1/2020	1500

[v-jayw-msft · Accepted solution]

Hi @anuxps ,

Think you need to add an index column.

Check the formula and out put after add an index column.

Measure = CALCULATE(SUM('Table'[Expense]),FILTER(ALLEXCEPT('Table','Table'[Department]),'Table'[Index]<=MAX('Table'[Index])))

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[amitchandak]

@anuxps , Try like

Cumm Expense = CALCULATE(SUM(Table[Expense]),filter(all(date),date[date] <=maxx(date,date[date])))

Cumm Expense = CALCULATE(SUM(Table[Expense]),filter(all(date),date[date] <=maxx(date,date[date]),
filter(all(Department),Department[Department] <=maxx(Department,Department[Department]))))

[anuxps]

It is not working. All these columns are present in one table called Expenses

[amitchandak]

@anuxps , Those should work as Measure with date table.

Try this as new column

cumm = sumx(filter(Expenses, [Department] = earlier([Department]) && [ExpenseDate] <= earlier([ExpenseDate])),[Expense])

You can add additional condition to filter

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[anuxps]

See here is the result coming which is not correct for Sales Department if you see

Department	Month	Year	Expense	Measure	ExpenseDate	cumm
HR	Feb	2020	500	500	2/1/2020	1500
HR	Jan	2020	1000	1500	1/1/2020	1000
HR	Mar	2020	300	1800	3/1/2020	1800
MFG	Feb	2020	500	1000	2/1/2020	500
MFG	Mar	2020	600	2900	3/1/2020	1100
Sales	Feb	2020	700	1700	2/1/2020	1600
Sales	Jan	2020	100	2800	1/1/2020	100
Sales	Mar	2020	700	4400	3/1/2020	3000