Forum Discussion
rsderby68
Resolver I
Resolver IMonthly 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...
ERD
Community Champion
Community ChampionIn 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 )
rsderby68Resolver I
Resolver ISorry 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.