Forum Discussion

newguy's avatar
newguy
New Member
4 years ago
Solved

Issue With Rolling 3 Month Average

I know there are alot of rolling average posts out there, but I haven't been able to find a solution to my issue. I am trying to get a 3 month rolling average of revenue, example below:   For...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi newguy 
    Would you please double check and confirm if the date table is marked as date table. In all cases would you please add REMOVEFILTERS ( D_Date ) inside CALCULATE and let me know if you get any different results. 

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi newguy 
Please try

Net Revenue R3M :=
VAR NumOfMonths = 3
VAR LastSelectedDate =
    MAX ( D_DATE[Calendar_Date] )
VAR Period =
    DATESINPERIOD ( D_DATE[Calendar_Date], LastSelectedDate, - NumOfMonths, MONTH )
VAR Result =
    CALCULATE (
        AVERAGEX (
            SUMMARIZE ( D_DATE, D_DATE[Month Year], D_DATE[Year] ),
            [Total Net Revenue by DOS]
        ),
        Period
    )
RETURN
    Result
  • newguy's avatar
    newguy
    New Member
    4 years ago

    tamerj1 I tried using the SUMMARIZE function but ended up with the same result. I'm wondering if it an issue with the underlying data model or something. Is there potentially a different way to get the same rolling average by month result?

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      newguy 

      I don't think anything wrong with the model. Please try

      Net Revenue R3M :=
AVERAGEX (
    SUMMARIZE ( D_DATE, D_DATE[Month Year], D_DATE[Year] ),
    CALCULATE (
        VAR NumOfMonths = 3
        VAR LastSelectedDate =
            MAX ( D_DATE[Calendar_Date] )
        VAR Period =
            DATESINPERIOD ( D_DATE[Calendar_Date], LastSelectedDate, - NumOfMonths, MONTH )
        VAR Result =
            CALCULATE (
                AVERAGEX ( VALUES ( D_DATE[Month Year] ), [Total Net Revenue by DOS] ),
                Period
            )
        RETURN
            Result
    )
)
      • newguy's avatar
        newguy
        New Member
        4 years ago

        tamerj1 Same issue unfortunately. Let me make sure I am using the correct date fields in your example. Here are some of my date table fields and an example value from the table:

         

        *Calendar_Date = 1/1/2022 12:00:00 AM

        *Calendar_Month = 1

        *Calendar_Year = 2022

        *Month_Year = January 2022

        *Year_Month = 2022-01

         

        I tried switching the SUMMARIZE function to be

        SUMMARIZE(D_DATE,D_DATE[Calendar_Month], D_DATE[Calendar_Year])

        as well and didnt have any luck.