Earlier I wrote about this "road casualties" dashboard (in Dutch) where I used the custom visual "Play Axis" to loop through the years and display the number of casualties per year. Where most of the visuals only show the numbers of the selected year, I also wanted two visuals to display all figures until the selected year, so you can also have an insight in how the figures differ per year.
The problem
Since the "Play Axis visual" loops through the years one-by-one, it is not possible to fill these running graphs depending on a normal relationship. In this case, there is no defined relationship between my "year" table (the one that is used by Play Axis) and the year in my casualties table ('verkeersdoden m+v'[Perioden]) in my pbix file. So I needed to create a variable that FILTERs the number of deaths ('verkeersdoden m+v'[Verkeersdoden (aantal)]) before the selected year ([_selectedJaar]).
The solution
This is the DAX code I came up with:
Not too hard, is it? It basically SUMs the number of deaths up until the given year. You can see that the FILTER filters all values less or equal to the selected year. So we get the values of all traffic casualties that happened in the selected year, or in the recorded years prior to the selected year. But wait, what is the + 0. Why is that? I'll tell you. It's to make sure that the graphs "width per year" figures stay constant through all years. E.g. if I only select the first two years, than PowerBI will fill the whole available space with these two years. It will all get messy since when other years are added, the width per year will get less with each year added.
By adding zero (+0) to each calculation, there is a value for each year -even if it is after the selected year- and thus there is a fixed space reserved for each year.
It's a little bit of a hassle, but it's worth the time. Sometimes the "zero's" are a pain in the ***, but in this case it really comes in handy, as it makes it possible to make a smooth running graph.
So, this is the end result. You can see there is a nice, steady built-up of the visual. It grows with every selected year.
Combined with the "Play Axis" custom visual, this gives a nice running visualization of the data through the years.
