Forum Discussion

mbegg's avatar
mbegg
Advocate 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 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?

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

3 Replies

  • GilbertQ's avatar
    GilbertQ
    Super 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
      Advocate 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)
      )
  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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