cancel
Showing results for
Did you mean:
Regular Visitor

## Help With Running Total & Reseting Each Year

Hi All,

I'm attempting to create a running total calculation that would reset to zero each year.

Here's what I've got so far and it works but never resets...

```Bad Debt Reserve Running Balance = CALCULATE (
FILTER (
ALL ( DateMaster[Date] ),
DateMaster[Date] <= MAX ( DateMaster[Date] )
)
)```

I also need to add in the "Beginning Balance" at the start of the year from another table. So for example:

 yearMonth Bad Debt Reserve Beginning Balance Bad Debt Reserve Adjustment Bad Debt Reserve Running Balance 1/1/2016 -\$1,433,000 \$40,731 -\$1,392,269 2/1/2016 \$0 \$25,000 -\$1,367,269 3/1/2016 \$0 -\$101,131 -\$1,468,400 4/1/2016 \$0 \$21,700 -\$1,446,700 5/1/2016 \$0 -\$34,700 -\$1,481,400 6/1/2016 \$0 \$600 -\$1,480,800 7/1/2016 \$0 \$166,400 -\$1,314,400 8/1/2016 \$0 -\$74,500 -\$1,388,900 9/1/2016 \$0 -\$212,400 -\$1,601,300 10/1/2016 \$0 \$152,100 -\$1,449,200 11/1/2016 \$0 \$136,800 -\$1,312,400 12/1/2016 \$0 -\$27,600 -\$1,340,000 1/1/2017 -\$1,501,000 -\$54,500 -\$1,555,500

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

## Re: Help With Running Total & Reseting Each Year

Solved - as it turns out, there is nothing wrong with the DAX formula. It is a limitation on the display of Tool Tips. I was showing four (4) years of data and due to limits on the Tool Tip display it was cutting off the current YTD number. I changed the Page level filter to only show three (3) years and the current YTD number is shown as expected.

4 REPLIES 4
Established Member

## Re: Help With Running Total & Reseting Each Year

Hi @jdugas

You need a proper Year To Date calculation for your first requirement.

```Bad Debt Reserve Running Balance = CALCULATE (
FILTER (
ALL ( DateMaster[Date] ),
DateMaster[Date] <= MAX ( DateMaster[Date]         && DateMaster[Year] = MAX (DateMaster[Year]) )
)
)```

I added a filter condition on the year otherwise you compute [Bad Debt Reserve] for all the dates which are prior to the date in your current filter context, including those from previous year(s) [which is NOT what you want here).

You can also use built-in Time Intelligence dax functions (called DAX Sugar ) using this pattern:

1. Calculate ( [Measure] , DatesYTD ( Calendar[Date] )

or

2. TotalYTD ( [Measure] , Calendar[Date] )

I encourage you to use 1. because you can change the year end date with the 2nd (optional) parameter of DatesYTD function (very convenient when you are not computing values on calendar but fiscal years for example) and it explicitely shows how the formula works (with a calculate!!).

In this super article, @MattAllington perfectly explainsTime Intelligence in Power BI and shows the pros and cons of built-in Time Intelligence Dax functions.

Senior Member

## Re: Help With Running Total & Reseting Each Year

FWIW, TOTALYTD also includes an optional year end parameter and in my opinion is a better choice as it makes the code a tad easier to read. Here is function syntax:

`TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>]) `

As far as how to get the beginning balance included, that should be easy if the separate beginning balance table is related to the same "lookup" tables as the detail transaction table. Just add the two measures together.

```BB Amount = SUM ( BB Table[Amount] )
Running Total = TOTALYTD ( [BB Amount] + [Bad Debt Reserve Running Balance], Calendar[Date] )```
Frequent Visitor

## Re: Help With Running Total & Reseting Each Year

I am having a problem with this DAX formula - it does not show the YTD number for the current year. Any help would be appreciated.

YTD TicketSales =
FORMAT (
(
CALCULATE (
[Total Paid Tickets],
FILTER (
ALL ( dimDates ),
dimDates[Fiscal Year] = MAX ( dimDates[Fiscal Year] )
&& dimDates[CalendarDate] <= MAX ( dimDates[CalendarDate] )
)
)
),
"#,##0"
)

I am using this formula in the Tool Tip area and the display looks sort of like this:

Week 6

Fiscal Year 2014

Total Paid Tickets    50,000

YTD Ticket Sales     100,000

Fiscal Year 2015

Total Paid Tickets    60,000

YTD Ticket Sales     110,000

Fiscal Year 2016

Total Paid Tickets    40,000

YTD Ticket Sales     105,000

Fiscal Year 2017

Total Paid Tickets    55,000

*Missing YTD Number that should appear here?

Highlighted
Frequent Visitor

## Re: Help With Running Total & Reseting Each Year

Solved - as it turns out, there is nothing wrong with the DAX formula. It is a limitation on the display of Tool Tips. I was showing four (4) years of data and due to limits on the Tool Tip display it was cutting off the current YTD number. I changed the Page level filter to only show three (3) years and the current YTD number is shown as expected.