Forum Discussion
Problem with Moving Annual Total
You could use the year to date function rather than calculate - https://msdn.microsoft.com/en-us/library/ee634400.aspx
you should just need one new column in your date table which has the end of the month date to pass to the function.You can calculate that using
Based on the posts in this thread it is unclear if you need a [YTD Last Year] or a [Rolling Year].
Here's both with built in time intelligence (normal date dimension requirements here):
SumAmt = SUM( FactStupid[Amount] ) RollingYear = CALCULATE( [SumAmt] ,DATESINPERIOD( DimDate[Date] ,MAX( DimDate[Date] ) ,-1 ,YEAR ) ) YTD = TOTALYTD( [SumAmt] ,DimDate[Date] ) YTD Last Year = CALCULATE( [YTD] ,SAMEPERIODLASTYEAR( DimDate[Date] ) )
These have the benefit of working from any reference year, rather than only working only for last year based on today's date.
You can set a filter for CurrentYear = True on the page or report and these will function appropriately. Or you can set a filter for CurrentYTD = True
Im looking after the Rolling Year.
In my Datetable I've created columns which yield in TRUE if a certain condition is fullfilled. This columns are dynamic, since I do not want to change the filter context each time when refreshed. For example the column for last year is calculated as follows:
LastYear = if(Year(DATEADD('Calendar'(Date);12;MONTH))=Year(Today());"TRUE")
Now I want to do a visual with lines or stocked columns for each product subcategorie, which display as well the rolling year started in the month before. In the datetable this is created by:
LYM-1 = if(Year(DATEADD('Calendar'(Date);13;MONTH))=Year(Today());"TRUE")
Based on this columnes I can achieve the value for the period in question. The measures look like:
LYSales = Calculate(Sum(Sales(SalesAC));Filter(all('Calendar');'Calendar'(LastYear)="TRUE"))
LYM-1Sales = Calculate(Sum(Sales(SalesAC);Filter(all('Calendar');'Calendar'(LYM-1)="TRUE"))
But I'm not able to visualize this in a stacked column or line chart, where each column, respective each datapoint on the line represents a rolling year(eg. LYSales and LYM-1Sales). Do you have any ideas how to solve this problem?
Thanks for your help.
I've now got no idea where your problem lies. Is it in the measure logic, or in getting the appropriate subset of values to display on the axis?
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.
