Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Previous Rolling Average from Rolling Average Quick Measure

Hi,

 

I've created a Rolling Average on sales using the quick measure feature on my date column.

I was given the pre-defined DAX code and I amended the intervals for DateAdd function so that it is dynamic and changes based on whether we look back 3, 6 or 12 months as an average using "Dynamic Rolling Average Value" as per below:

 

Rolling Average All_Sales =
IF(
    ISFILTERED('All_Sales'[All_Invoice_Date]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    VAR __LAST_DATE = ENDOFMONTH('All_Sales'[All_Invoice_Date].[Date])
    VAR __DATE_PERIOD =
        DATESBETWEEN(
            'All_Sales'[All_Invoice_Date].[Date],
            STARTOFMONTH(DATEADD(__LAST_DATE, 'Dynamic Rolling Average'[Dynamic Rolling Average Value], MONTH)),
            __LAST_DATE
        )
    RETURN
        AVERAGEX(
            CALCULATETABLE(
                SUMMARIZE(
                    VALUES('All_Sales'),
                    'All_Sales'[All_Invoice_Date].[Year],
                    'All_Sales'[All_Invoice_Date].[QuarterNo],
                    'All_Sales'[All_Invoice_Date].[Quarter],
                    'All_Sales'[All_Invoice_Date].[MonthNo],
                    'All_Sales'[All_Invoice_Date].[Month]
                ),
                __DATE_PERIOD
            ),
            CALCULATE([All_Sales], ALL('All_Sales'[All_Invoice_Date].[Day]))
        )
)

I am stuck on creating the previous rolling average which simply needs to be the same calculated rolling average but brought forward from previous months, depending on the dynamic selection of the time period. E.g. below is the output table I would desire ("Previous..." header columns is the measure to be created:

Can someone guide how to do to this, as I am stuck after various iterations to attempt to get this?
 
MonthR3MPrevious R3MR6M Previous R6M
Jan-23202470 202470 
Feb-23215690 215690 
Mar-23230155 230155 
Apr-23235618202470227331 
May-23249727215690236112 
Jun-23257043230155243599 
Jul-23269503235618252561202470
Aug-23260043249727254885215690
Sep-23244012257043250528230155
Oct-23235273269503252388227331
Nov-23235773260043247908236112
Dec-23239455244012241733243599
Jan-24234692235273234983252561
Feb-24236442235773236108254885
Mar-24235293239455237374250528
Apr-24248658234692241675252388
May-24237097236442236769247908
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

    Based on your description, you need to create a calendar table first.

    Calendar = CALENDAR(DATE(2023,1,1),DATE(2024,5,1))

    Then create a relationship between data table and calendar table.

     

    Then create a measure

    PreRM =
CALCULATE (
    [Rolling Average All_Sales],
    DATEADD (
        'Calendar'[Date],
        - MAX ( 'Dynamic Rolling Average'[Dynamic Rolling Average Value] ),
        MONTH
    )
)

    Then put the date column of calendar table  and the measure to the visual.

     

    Output

     

     

    Best Regards!

    Yolo Zhu

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

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

    Based on your description, you need to create a calendar table first.

    Calendar = CALENDAR(DATE(2023,1,1),DATE(2024,5,1))

    Then create a relationship between data table and calendar table.

     

    Then create a measure

    PreRM =
CALCULATE (
    [Rolling Average All_Sales],
    DATEADD (
        'Calendar'[Date],
        - MAX ( 'Dynamic Rolling Average'[Dynamic Rolling Average Value] ),
        MONTH
    )
)

    Then put the date column of calendar table  and the measure to the visual.

     

    Output

     

     

    Best Regards!

    Yolo Zhu

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