Forum Discussion

mbegg's avatar
mbegg
Icon for Advocate II rankAdvocate II
9 years ago
Solved

Card with trailing 12 month average

Hi,    I have monthly sales data and a date table. I have calculated trailing 12 month (TTM) average sales.    I want to display TTM for most recent month on a card on a report.    If I add a f...
Need Help
  • mbegg's avatar
    mbegg
    9 years ago

    GilbertQ thanks for the suggestion. 

     

    I found the following worked:

    • Create a column in the date table with 1s & 0s to flag last 12 months.
    • Create the below measure

     

    Sales_ttm avg. = 
    AVERAGEX(
	KEEPFILTERS(VALUES('Date'[Date].[Date])),
	CALCULATE(SUM('Sales'[Value]),'Date'[FLAG_TTM]=1)
    )
GilbertQ's avatar
GilbertQ
Icon for Super User rankSuper User
9 years ago

Hi mbegg

 

What I would suggest doing is to create your measure where the most recent month is factored into your measure. So that when you put it into your card visual you will not have to still create a filter.


What happens is when you put in a filter, it changes your measures filter context to apply the filter that you added, which is why it then recalculates to only use 1 months worth of data.

mbegg's avatar
mbegg
Icon for Advocate II rankAdvocate II
9 years ago

GilbertQ thanks for the suggestion. 

 

I found the following worked:

  • Create a column in the date table with 1s & 0s to flag last 12 months.
  • Create the below measure

 

Sales_ttm avg. = 
AVERAGEX(
	KEEPFILTERS(VALUES('Date'[Date].[Date])),
	CALCULATE(SUM('Sales'[Value]),'Date'[FLAG_TTM]=1)
)