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...
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 ) )
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!!