Forum Discussion
Solved
Rolling yearly Average
Hello I am trying to calculate a rolling average. Its to calculate the yearly usage of a certain product over the last rolling 12 months and then from there I will calculate the daily average by ...
Hi AA622 ,
Please update the formula of your measure [UsageRolling6mo] as below and check if it works or not...
UsageRolling6mo = VAR __MaxDate = TODAY () VAR __EOM12 = EOMONTH ( __MaxDate, -12 ) VAR __MinDate = DATE ( YEAR ( __EOM12 ), MONTH ( __EOM12 ), DAY ( __MaxDate ) ) VAR __Table = FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date] <= __MaxDate && 'Calendar'[Date] >= __MinDate ) RETURN AVERAGEX ( __Table, [Usage] )In addition, you can refer the following links to get the rolling average values:
Rolling 12 Months Average in DAX
Best Regards
Better RA =
VAR __MaxDate = TODAY()
VAR __EOM12 = EOMONTH(__MaxDate,-12)
VAR __MinDate = DATE(YEAR(__EOM12),MONTH(__EOM12),DAY(__MaxDate)
VAR __Table = FILTER(ALLSELECTED('Table'),[Date] <= __MaxDate && [Date] >= __MinDate)
RETURN
AVERAGEX(__Table,[Usage])
HAving issues with the last VAR line. Line 5
Am I supposed to reference a table?
I tried referencing our Calendar Table. What would I put at the last line? I also seem to be having an issue with the mindate VAR in line 5
Hi AA622 ,
Please update the formula of your measure [UsageRolling6mo] as below and check if it works or not...
UsageRolling6mo =
VAR __MaxDate =
TODAY ()
VAR __EOM12 =
EOMONTH ( __MaxDate, -12 )
VAR __MinDate =
DATE ( YEAR ( __EOM12 ), MONTH ( __EOM12 ), DAY ( __MaxDate ) )
VAR __Table =
FILTER (
ALLSELECTED ( 'Calendar' ),
'Calendar'[Date] <= __MaxDate
&& 'Calendar'[Date] >= __MinDate
)
RETURN
AVERAGEX ( __Table, [Usage] )In addition, you can refer the following links to get the rolling average values:
Rolling 12 Months Average in DAX
Best Regards