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.

     

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.