cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Help with running total

Hi,

I need the running total to start over for every new months, example:

 Date Sales cumulative total 01-01-2020 10 10 02-01-2020 20 30 03-01-2020 10 40 04-01-2020 5 45 05-01-2020 10 55 29-01-2020 5 60 30-01-2020 15 75 31-01-2020 5 80 01-02-2020 10 10 02-02-2020 15 25 03-02-2020 5 30

```Running total=

CALCULATE (
SUM ( Table1[Reserve Beginning Balance] ),
FILTER (
ALL ( Table1 ),
Table1[Date] <= MAX ( Table1[Date] )
)
)```

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Community Support

## Re: Help with running total

Hi @aagj ,

At first, you need to create a new column to get "YYYY-MM".

``````YM =
FORMAT('Table'[Date],"yyyymm")``````

Then you could use column or measure to get the running total.

Column:

``````Column =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
'Table',
'Table'[YM] = EARLIER ( 'Table'[YM] )
&& 'Table'[Date] <= EARLIER ( 'Table'[Date] )
)
)``````

Measure:

``````Measure =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[YM] = MAX ( 'Table'[YM] )
&& 'Table'[Date] <= MAX ( 'Table'[Date] )
)
)
``````

Here is my test file for your reference.

If this post helps, then please consider Accept it as the solution to help the other members find it.
2 REPLIES 2
Highlighted Super User IX

## Re: Help with running total

@aagj , Use MTD with date calendar

MTD Sales = CALCULATE(SUM(Table1[Sales]),DATESMTD('Date'[Date]))

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://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

Proud to be a Super User!

Highlighted Community Support

## Re: Help with running total

Hi @aagj ,

At first, you need to create a new column to get "YYYY-MM".

``````YM =
FORMAT('Table'[Date],"yyyymm")``````

Then you could use column or measure to get the running total.

Column:

``````Column =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
'Table',
'Table'[YM] = EARLIER ( 'Table'[YM] )
&& 'Table'[Date] <= EARLIER ( 'Table'[Date] )
)
)``````

Measure:

``````Measure =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[YM] = MAX ( 'Table'[YM] )
&& 'Table'[Date] <= MAX ( 'Table'[Date] )
)
)
``````

Here is my test file for your reference.

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