Forum Discussion

rsderby68's avatar
rsderby68
Icon for Resolver I rankResolver I
3 years ago
Solved

Monthly Rolling Average Headaches

Hello All, 

 

I am stuck trying to figure out how to modify the rolling average quickmeasure to include monthly averages in my table, below.  When I created the quickmeasure, it is indeed averaging the total (see table total) BUT what I want it to do is include in each rolling average row the average per month (based of any months before each row.  However, all it returns is the same values of the count they are averaging.  Can someone help me figure this out?  I included the DAX below.  

 

So in my table the rolling average rows should include the average for may, then the average for may and june, then the average for may, june, and july, etc.  

 

Help? 

 

 

Monthly New Registrant Rolling Average =
IF(
    ISFILTERED('Vimeo_Customers'[Vimeo_Customer_Create_DateTime]),
    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('Vimeo_Customers'[Vimeo_Customer_Create_DateTime].[Date])
    VAR __DATE_PERIOD =
        DATESBETWEEN(
            'Vimeo_Customers'[Vimeo_Customer_Create_DateTime].[Date],
            STARTOFMONTH(DATEADD(__LAST_DATE, -12, MONTH)),
            __LAST_DATE
        )
    RETURN
        AVERAGEX(
            CALCULATETABLE(
                SUMMARIZE(
                    VALUES('Vimeo_Customers'),
                    'Vimeo_Customers'[Vimeo_Customer_Create_DateTime].[Year],
                    'Vimeo_Customers'[Vimeo_Customer_Create_DateTime].[QuarterNo],
                    'Vimeo_Customers'[Vimeo_Customer_Create_DateTime].[Quarter],
                    'Vimeo_Customers'[Vimeo_Customer_Create_DateTime].[MonthNo],
                    'Vimeo_Customers'[Vimeo_Customer_Create_DateTime].[Month]
                ),
                __DATE_PERIOD
            ),
            CALCULATE(
                COUNTA('Vimeo_Customers'[Vimeo_Customer_ID]),
                ALL('Vimeo_Customers'[Vimeo_Customer_Create_DateTime].[Day])
            )
        )
)
Help
  • ERD's avatar
    ERD
    3 years ago

    rsderby68 , please, change the measure:

    ID_rolling_avg =
VAR period = DATESBETWEEN ( 'Date'[Date], DATE ( 2022, 05, 15 ), MAX ( 'Date'[Date] ) ) 
RETURN
    IF ( MIN ( 'Date'[Date] ) <= TODAY (), CALCULATE ( [ID_avg], period ) )

5 Replies

  • ERD's avatar
    ERD
    Icon for Community Champion rankCommunity Champion
    3 years ago

    rsderby68 ,

    In order to use time intelligence calculations mentioned in your example, you need a well-formed date table. I don't know the particularities of your data, but this is what you can try:

    1. Create a Date table.

    2. Create a Date column in your data table to use it for connection with the Date table.

    3. Connect these 2 tables by Date column.

    4. Create 2 measures:

    ID_avg = 
AVERAGEX (    
    ADDCOLUMNS (
        SUMMARIZE ( Vimeo_Customers, 'Date'[Year], 'Date'[Month Name] ),
        "@ids", CALCULATE ( COUNT ( Vimeo_Customers[Vimeo_Customer_ID] ) )
    ),
    [@ids]
) 
    ID_rolling_avg = 
VAR period =
    DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -1, YEAR )
RETURN
    CALCULATE ( [ID_avg], period )

     

    • rsderby68's avatar
      rsderby68
      Icon for Resolver I rankResolver I
      3 years ago

      Hi ERD, 

       

      I implemented the measures you listed and got this result below.  I think the included dates on the rolling average is off because those numbers aren't correct averages.  To specify, I want the average calculations to start in May-2022 (which is when we launched our new version) even thought there is data beforehand.  And all other averages should build on that moving in the future from May-2022.  Should I change something in the measure to lock that in?   Note I do have a full date dates linked to my other tables (see below).  

       

      Thanks for your help! I think I am so close!! 

       

       

       

       

    • rsderby68's avatar
      rsderby68
      Icon for Resolver I rankResolver I
      3 years ago

      I will also note that my date tables goes way into the future so I only want to include results where there is actual customer_id data which would only be the past (but I want to lock in start of 5/15/2022 launch) and up until 'today' date(today) whatever the current day is.  If that makes sense?  

    • rsderby68's avatar
      rsderby68
      Icon for Resolver I rankResolver I
      3 years ago

      Sorry for all the appending messages but here is the full table.  I think it is correct except I need to lock in a start date for the averages of may-2022 or 05/15/2022 if it can be that specific.  

       

       

      • ERD's avatar
        ERD
        Icon for Community Champion rankCommunity Champion
        3 years ago

        rsderby68 , please, change the measure:

        ID_rolling_avg =
VAR period = DATESBETWEEN ( 'Date'[Date], DATE ( 2022, 05, 15 ), MAX ( 'Date'[Date] ) ) 
RETURN
    IF ( MIN ( 'Date'[Date] ) <= TODAY (), CALCULATE ( [ID_avg], period ) )