Forum Discussion
Resolver IMonthly Rolling Average Headaches
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 )
rsderby68Hi 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
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
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.