Forum Discussion

rjones's avatar
rjones
Frequent Visitor
6 years ago
Solved

12 month moving total trend chart

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 ))
Need Help
Tips and Tricks
  • rjones's avatar
    rjones
    6 years ago

    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]

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      rjones
      Frequent Visitor
      6 years ago

      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's avatar
        rjones
        Frequent Visitor
        6 years ago

        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]