Hello, I created several Columns in my Date-Table wich indicate dynamically if a date belongs to a certain period. This is important, because I do not want to change the filtering conditions all the time the report has to be refreshed. I have then set up a measure for the moving annual Total in the following way: Cummulated Sales = Calculate(Sum(Sales[SalesAC]);Filter('Calendar';'Calendar'[LastYear]="TRUE")) but this does not cummulate the values. If I change the measure to: Cummulated Sales 2 = Calculate(Sum(Sales[SalesAC]);Filter(all('Calendar');'Calendar'[LastYear]="TRUE")) it does sum up the values correctly for the period in question, but I have the value also for all other dates out of this period. How can I solve this problem? I just want a measure, which allows me to set a dynamic start and end date for the period which needs to be cummulated and is just displayed in this period. Thanks for your anwers.

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

I don't think the ALL should be inside the filter try something like Calculate(Sum(Sales[SalesAC]),Filter('Calendar'[LastYear]="TRUE"),all('Calendar')) It may also be worth looking at the DAX time intelligence fucntions. I have a measure that returns sum for previous year that looks like Calculate(SUM(Tablename[col]),previousyear('Calendar'[End Date],"August 31"),ALL('Calendar')) If you want a period other than pervious year there are other functions to define periods dynamically such as datesinperiod or datesbetween - https://msdn.microsoft.com/en-us/library/ee634763.aspx

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.

Thanks for your answer, but the result is the same as I totally omit the all-Filter. The problem is actually this one: I want to produce a visual with a moving annual total for each month. So, in January I want to see on the visuale a stacked column with the different productsubcategories for the range from Dec 14 - Dec 15, as well as from Nov 14 - Nov 15, and so on. Is there any possibility to do a visual like that? Thanks for you help.

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 https://support.office.com/en-us/article/ENDOFMONTH-Function-DAX-57764a21-0e3b-419b-b9ed-0bc91cf3d75c

Problem with Moving Annual Total | Microsoft Fabric Community

11 Replies

itchyeyeballs
Impactful Individual
10 years ago
I don't think the ALL should be inside the filter

try something like

Calculate(Sum(Sales[SalesAC]),Filter('Calendar'[LastYear]="TRUE"),all('Calendar'))

It may also be worth looking at the DAX time intelligence fucntions. I have a measure that returns sum for previous year that looks like

Calculate(SUM(Tablename[col]),previousyear('Calendar'[End Date],"August 31"),ALL('Calendar'))

If you want a period other than pervious year there are other functions to define periods dynamically such as datesinperiod or datesbetween - https://msdn.microsoft.com/en-us/library/ee634763.aspx
- Patrick_Knobel
  New Member
  10 years ago
  Thanks for your answer, but the result is the same as I totally omit the all-Filter.
  
  The problem is actually this one:
  
  I want to produce a visual with a moving annual total for each month. So, in January I want to see on the visuale a stacked column with the different productsubcategories for the range from Dec 14 - Dec 15, as well as from Nov 14 - Nov 15, and so on. Is there any possibility to do a visual like that?
  
  Thanks for you help.
  - itchyeyeballs
    Impactful Individual
    10 years ago
    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
    
    https://support.office.com/en-us/article/ENDOFMONTH-Function-DAX-57764a21-0e3b-419b-b9ed-0bc91cf3d75c

Forum Discussion

Problem with Moving Annual Total

11 Replies

Related content

Moving Average of Moving Annual Total Value

moving average help

Prorated annual expense

Logic problem?

Slider problems