Forum Discussion
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.
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 ?
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