Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calculation 12 months slide

Hello, I want to do a calculation over a rolling period of 12 months on Power BI. I would like to retrieve this measure for each month over a rolling 12-month average measure that I already have.

For example, if I select the month of December 2023, I retrieve the last 12 values (from previous months = December 2022) of averages and divide by 12 the whole.

I have already try this but it's return me only the [TotalAverage] values : 

CALCULATE(
    ([TotalAverage]),
    DATESINPERIOD('SourceMCP'[Month], MAX('SourceMCP'[Month]), -12, MONTH)
)
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    Please try code as below.

    Rolling Prev 12 Month =
VAR _RANGEEND =
    MIN ( 'Date'[Date] )
VAR _RANGESTART =
    EOMONTH ( MIN ( 'Date'[Date] ), -13 )
RETURN
    CALCULATE (
        [TotalAverage],
        FILTER (
            ALL ( 'Date' ),
            'Date'[Date] > _RANGESTART
                && 'Date'[Date] < _RANGEEND
        )
    )

    Result is as below. When I select Dec 2023, measure will return the average between 12/1/2022 and 11/30/2023.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

4 Replies

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

    Anonymous , use date table in visual, measure and slicer

     

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

     

    or

     

    Rolling 12 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],eomonth(MAX('Date'[Date]),-1) ,-12,MONTH))

     

     

    Rolling 12 = CALCULATE([Net], WINDOW(-11,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))

     

    or

     

    Rolling 12 = CALCULATE([Net], WINDOW(-12,REL, 1-, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))

     

    Rolling Months Formula: https://youtu.be/GS5O4G81fww

    Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
    https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

     

    Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
    https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thank you for your answer and your links tutorial ! 

      I have a question what relation can i make between my date table and my other table with all my data because i did One to many relationship between them but it doesn't working ?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Anonymous ,

         

        Please try code as below.

        Rolling Prev 12 Month =
VAR _RANGEEND =
    MIN ( 'Date'[Date] )
VAR _RANGESTART =
    EOMONTH ( MIN ( 'Date'[Date] ), -13 )
RETURN
    CALCULATE (
        [TotalAverage],
        FILTER (
            ALL ( 'Date' ),
            'Date'[Date] > _RANGESTART
                && 'Date'[Date] < _RANGEEND
        )
    )

        Result is as below. When I select Dec 2023, measure will return the average between 12/1/2022 and 11/30/2023.

         

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

         

  • VdeJong's avatar
    VdeJong
    Frequent Visitor
    1 year ago

    There is also another solution, which I use and it works. I did it for 6 month rolling average, so the figure of 6 month prior to july would be like underneath. I made to measures and ofcourse you can do it in one measure:

    Measure 1

     

    Moving average extra =

    VAR Period =

     DATESINPERIOD('DateTable'[Date],

                    MAX('DateTable'[Date]),

                    -6,MONTH)

    Var Mon =

        CALCULATE(DISTINCTCOUNT('DateTable'[Month]),

        Period)

    VAR Totinvoice =

        CALCULATE(SUM('FINDB_DMT FCT_INVC_LINE_DTLS_HIST'[INVOICED_TONNAGE]),

        Period)

    VAR Aver6month =

        Divide(Totinvoice,Mon)

    RETURN

       (Aver6month)

     

    Measure 2

    Moving Aver Offset =

        CALCULATE([Moving average extra],

        PREVIOUSMONTH('DateTable'[Date]))