Forum Discussion
Moving Average - not date based
this is so nearly there, 1 issue, when there are 0 incidents in a period this happens
so we need the calc to process the '0' rows as '0' unless greater than period 5 - where we have no data as its in the future
the above is ignoring legitmate '0s' as blanks
dantheram
Please try
Incident Count MA =
VAR LastDateWithData =
CALCULATE ( MAX ( 'Table'[Date] ), REMOVEFILTERS () )
VAR CurrentDate =
MAX ( 'Date'[Date] )
RETURN
IF (
CurrentDate <= LastDateWithData,
VAR CurrentRank =
MAX ( 'Date'[Fiscal Period Rank] )
VAR T1 =
FILTER (
ALLSELECTED ( 'Date'[Fiscal Period Rank] ),
'Date'[Fiscal Period Rank] <= CurrentRank
&& 'Date'[Fiscal Period Rank] >= CurrentRank - 12
)
RETURN
AVERAGEX (
T1,
VAR CurrentRank2 = 'Date'[Fiscal Period Rank]
RETURN
CALCULATE (
[Incident Count],
REMOVEFILTERS ( 'Date' ),
'Date'[Fiscal Period Rank] = CurrentRank2
)
)
)you sir are a hero 🙂
thanks so much!
the issue with the above is that the 'lastdatewithdata' and max current date lines will always be '13', as i have to use the period numbers and they reoccur each year - so there's always a 13.
so, it is working but still running on past period 5
to be honest i can live without it stopping at year to date - so thanks very much 🙂
but the fact table has periods 1 to 13 for previous years - i might be making an error but here is my version of your solution -
Incident Count MA2 = VAR LastDateWithData = CALCULATE ( MAX ( 'SAFs Actuals'[Period] ), REMOVEFILTERS () ) VAR CurrentDate = MAX ( 'Calendar'[Period2] ) RETURN IF ( CurrentDate <= LastDateWithData, VAR CurrentRank = MAX ( 'Calendar'[Fiscal Period Rank] ) VAR T1 = FILTER ( ALLSELECTED ( 'Calendar'[Fiscal Period Rank] ), 'Calendar'[Fiscal Period Rank] <= CurrentRank && 'Calendar'[Fiscal Period Rank] >= CurrentRank - 12 ) RETURN AVERAGEX ( T1, VAR CurrentRank2 = 'Calendar'[Fiscal Period Rank] RETURN CALCULATE ( [Incident Count 2], REMOVEFILTERS ( 'Calendar' ), 'Calendar'[Fiscal Period Rank] = CurrentRank2 ) ) )