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. Fo...
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.

     

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.