cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jdugas Regular Visitor
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 (
    [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:

 

yearMonthBad Debt Reserve Beginning BalanceBad Debt Reserve AdjustmentBad 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
hhuddleston Frequent Visitor
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
Datatouille Established Member
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 (
    [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 Smiley Happy ) 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 ?

mattbrice Senior Member
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] )
hhuddleston Frequent Visitor
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
hhuddleston Frequent Visitor
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.