12 month moving total trend chart

[rjones]

I got a 12 month trend working. I was wondering how to display that in a trend chart. If I filter the data for the chart to be from Jan-2018 to Current, the first month doesn't have the last 12 months to calculate. Only 12 months into the trend chart is the data accurate. I'm probably in the weeds on this one and it's just something simple I'm missing. Below is my formula. I also tried a last two years filter with the underlying data going back to 2017. Any filter also filters the data used in for the 12 month calculaton

CALCULATE ([Sales$], FILTER ( ALLSELECTED ('Totals'),'Totals'[Date] <=  MAX('Totals'[Date]) && DATEDIFF ( 'Totals'[Date],MAX('Totals'[Date]),MONTH ) <= 11 ))

[amitchandak]

Not very clear , but if you want every month to last 12 month sum or Avg

Try Like

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-12,MONTH))

For Average change aggregation of Measure

[rjones]

Thank You! I haven't asked many tech questions and this is my first here.  Is that two calcs below, so I should pick one?

Check out the hypothetical chart below. The underlying data goes to 1-1-2017. I have added a filter to the chart to only have 2020 and 2019. Starting in Jan-19 it starts to reduce by the amount of sales per month. So filtering the data also filters the 12 months used in the Moving Total. I was wondering if there's a way to still do this.

[rjones · Accepted solution]

I figured out one way to do it. Pre-Calculate the 12 month  totals before using a calculated measure in Power BI. Make sure you have a year's worth prior to the begining time frame of your chart. Here's some example SQL to calculate last 12 but including the current month. 

WITH CTE

     AS (SELECT   [Month] = <SalesDate>,

                  ActualBookings$ = SUM(Sales$)

         FROM     [dbo].[SalesTable]

         GROUP BY <SalesDate>,

     CTE1

     AS (SELECT   B.*,

                  Moving12MonthTot = SUM(A.Sales$)

         FROM     CTE A

                  JOIN CTE B

                    ON A.[Month] BETWEEN DATEADD(mm,-11,b.[Month])

                    AND b.[Month]

         GROUP BY b.[Month],

                  b.Sales$)

SELECT   *

FROM     CTE1

ORDER BY [Month]