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

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?