Forum Discussion
Issue With Rolling 3 Month Average
Community ChampionI don't think anything wrong with the model. Please try
Net Revenue R3M :=
AVERAGEX (
SUMMARIZE ( D_DATE, D_DATE[Month Year], D_DATE[Year] ),
CALCULATE (
VAR NumOfMonths = 3
VAR LastSelectedDate =
MAX ( D_DATE[Calendar_Date] )
VAR Period =
DATESINPERIOD ( D_DATE[Calendar_Date], LastSelectedDate, - NumOfMonths, MONTH )
VAR Result =
CALCULATE (
AVERAGEX ( VALUES ( D_DATE[Month Year] ), [Total Net Revenue by DOS] ),
Period
)
RETURN
Result
)
)
tamerj1 Same issue unfortunately. Let me make sure I am using the correct date fields in your example. Here are some of my date table fields and an example value from the table:
*Calendar_Date = 1/1/2022 12:00:00 AM
*Calendar_Month = 1
*Calendar_Year = 2022
*Month_Year = January 2022
*Year_Month = 2022-01
I tried switching the SUMMARIZE function to be
SUMMARIZE(D_DATE,D_DATE[Calendar_Month], D_DATE[Calendar_Year])
as well and didnt have any luck.
tamerj1 I really don't need it at the year level, only the month. Each month should show the average of the Net Revenue of the previous three months. So May would show the average of March/April/May.
Ideally I would have a card showing the 3 month average for the most recent month, or a line/bar combo graph showing the Total Revenue using the bars, and then the line would be the rolling average (something like the image below).
This specific calculation would also be used in another measure where I would take total payments in a given month, then divide it by the 3 month average net revenue to get a collection rate. Thanks!