Forum Discussion
Problem with Moving Annual Total
Hello togehther,
in the meantime I could solve the issue. The problem was, that I've created for each period a measure for the moving annual total and wanted to display it in a visual with a time axis and as legend the subcategories. This has not worked out, so I've built a measure which calculates the moving annual total for each period based on the imposed conditions on the period under question:
Here you find my measure, which looks a bit complicated, but basically it's just a 11 times nested if condition:
With this I was able to creat the visual I wanted. The only thing, I'm not fully convinced is the timing. Basically the start of the visual timeline is given by the starting date of the first period to sum up, which leads to the point, that the time axis is moved 1 year backwards. Here you find the visual:
If you have an idea to get to this result in an easier way I would appreciate your comments and ideas.
Patrick_Knobel, thanks for including the image of your desired end result. It is very helpful. It's clear now what you need.
Here's my reproduction of your visual:
A few things to note. The visual is a stacked column. The axis labels are simply my months. The measure is a simple SUM( FactStupid[Amount] ). The legend is my DimProduct[Model]. The only special sauce in the whole setup is a visual-level filter on my field DimDate[Rolling 12M] = True.
It looks like you've got fields in your date dimension already that can help fill this role, if not, it's trivial. Here's my DAX for the column:
Rolling 12M = VAR CurrentMonth = LOOKUPVALUE( DimDate[MonthIndex] ,DimDate[Date] ,TODAY() ) RETURN DimDate[MonthIndex] >= CurrentMonth - 11 && DimDate[MonthIndex] <= CurrentMonth
Where [MonthIndex] is an incrementing counter that indicates the number of months that have passed since the beginning of my calendar. In Power Query, here's how I create [MonthIndex] (as a custom added column):
= let Mult = [Year] - List.Min( MonthNumber[Year] ) ,Index = 12 * Mult + [MonthNumber] in Index
'MonthNumber[Year]' is the reference to the [Year] column in the previous step in Power Query. MonthNumber, in that context, refers to the name of the previous step.