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.

     

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