Card with trailing 12 month average

[mbegg]

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 flag to the data table for the most recent month, and then add that as a filter to the card, the TTM recalculates to use only 1 month worth of data. 

Any ideas?

[v-shex-msft]

Hi @mbegg,

I think you can try write a measure and use today function to get last date to filter on TTM measure.
If above not help, can you please share some sample data and the formula?

Regards,

Xiaoxin Sheng

[GilbertQ]

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 · Accepted solution]

@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)
)