Forum Discussion

Patrick_Knobel's avatar
Patrick_Knobel
New Member
10 years ago

Problem with Moving Annual Total

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 ...
Need Help
itchyeyeballs's avatar
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's avatar
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.

    • greggyb's avatar
      greggyb
      Resident Rockstar
      10 years ago

      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

       

      • Patrick_Knobel's avatar
        Patrick_Knobel
        New Member
        10 years ago

        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.