Running total

[Anonymous]

I am trying to get a calculate a running total per vendor. I am wanting to have each month sum all the previous months. 

For example, a date range from Jan 2018 - Aug 2018. I want to know for a specific vendor how many sales he had in Jan 2018 than in Feb I want to know how many sales he had in Feb + January and so on. 

I am trying to then graph it in a clustered column chart to show how many sales each vendor has sold. The graph should look like this. 

Jan - Total Sales for Jan

Feb - Total Sales for Jan + Feb

Mar - Total Sales for Jan + Feb + Mar

and so on....

Can someone help with my situation?

I have a running total formula showing. 

running total measure = CALCULATE(SUM(DataDump1[sales]),
                                           FILTER(ALLSELECTED(DataDump1),
                                               AND(DataDump1[Date] <= MAX(DataDump1[Date]),
                                                   YEAR(DataDump1[Date]) = YEAR(MAX(DataDump1[Date])))))

But this formula only gives me the total for that month.

[Anonymous]

@v-danhe-msft

Thank you for the solution, that is exactly what I was looking for! 

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

Based on my test, you could refer to below formula:

running total measure = CALCULATE(SUM(Table1[Sales]),
                                           FILTER(ALL(Table1),
                                               'Table1'[Date] <= MAX('Table1'[Date])
                                               &&
                                                   YEAR('Table1'[Date]) = YEAR(MAX('Table1'[Date]))))

Result:

You could download the pbix file to have a view, if the problem still occurs, could you please offer me some sample data to test?

https://www.dropbox.com/s/yitavv5yxuxt418/Running%20total.pbix?dl=0

Regards,

Daniel He

[Anonymous]

I get a running total when I am looking at the graph on a year level, but when I drill down into a month level it has the totals for those months broken out month by month.

[Anonymous]

@Anonymous

I ran that formula and got the same results back as the formula I shared to start the thread. Thank you helping with this. 

I am now wondering how to get the totals to add month over month. 

Jan - Total Sales for Jan

Feb - Total Sales for Jan + Feb

Mar - Total Sales for Jan + Feb + Mar

I am still getting the same graph as I had before.  Where it is just showing the total amount in that month. I want it to add the previous months into the current months running total. Any idea on how to complete this process or if its possible to do so?

Thank you so much for all your help!

[Anonymous]

Thank you for responding back, I don't feel comfortable sharing my file over the internet. Sorry. 

With the formula you shared, are you using a calendarTable? I am just wondering where 'P3: DimDate[formatMM] is coming from?

[Anonymous]

@Anonymous I have hooked it to a dim date table. Also you can create a quick measure there is a running total option.

[Anonymous]

@Anonymous Thank you for your response. 

Here is my graph. What I am trying to get it to do is calculate the sales in June, then in July has the sales from June be added to the sales in July. Then in Aug have the sales from Jun & Jul added to Aug.

Maybe I didn't explain the issue in my first post clearly and I apologize. But that is what I am trying to do. I may have the correct formula but I am not sure how to format my graph to show the way I am looking for it to show. I am also new to power bi and still learning as I go. Thanks for all the help

[Anonymous]

@Anonymous I understood what you are trying to do, could you share your power bi file so I can see what you are doing of it?

[Anonymous]

try something like this

CurrentSales running total in FormatMM =
CALCULATE(
SUM('P3: Previous Month Sales'[CurrentSales]),
FILTER(
ALLSELECTED('P3: DimDate'[FormatMM]),
ISONORAFTER('P3: DimDate'[FormatMM], MAX('P3: DimDate'[FormatMM]), DESC)
)
)

[Anonymous]

@Anonymous  your formula seems to be right, could you share the power bi file, if not could you create dummy data and recreate the problem with that and share it