Help With Running Total & Reseting Each Year

[jdugas]

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 (
    [Bad Debt Reserve],
    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

[Datatouille]

Hi @jdugas

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

Bad Debt Reserve Running Balance = CALCULATE (
    [Bad Debt Reserve],
    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.

Regarding your 2nd requirement, could you be more explicit please ?

[Anonymous]

I had been looking for this solution for like a whole day, I tried all combinations for this measure and nothing worked until you added that extra filter, which I had also tried, BUT you did have an = sign not an <= sign as I did...and  that did the trick!! thank you so much!! @Datatouille 

[mattbrice]

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] )

[hhuddleston]

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? 

[hhuddleston · Accepted solution]

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.