topic Re: Moving Average - not date based in DAX Commands and Tips

Moving Average - not date based

dantheram — Mon, 14 Nov 2022 17:53:55 GMT

hi all

i'm really struggling to understand how to compile what should be a very simple MAA. The issue is that i do not use date, i use period numbers; 1 to 13, as below -

all i want is a moving average (for incidents) across the last 13 periods, so for P5 above the value would be the average of periods; 6,7,8,9,10,11,12,13,1,2,3,4,5.

i just cannot get it to work, any help much appreciated

Re: Moving Average - not date based

Greg_Deckler — Mon, 14 Nov 2022 18:23:38 GMT

dantheram Try adding an index so that you have something to define "before"

Re: Moving Average - not date based

Jihwan_Kim — Mon, 14 Nov 2022 18:45:34 GMT

Hi,

Please check the below measure and the attached pbix file.

I tried to create a sample pbix file like the attached file.

Moving avg 13 periods: = VAR _currentperiod = MAX ( Data[Period] ) VAR _currentFYone = LEFT ( MAX ( Data[Fiscal Year] ), 4 ) * 1 VAR _newtableone = FILTER ( ALL ( Data ), Data[Fiscal Year] = MAX ( Data[Fiscal Year] ) && Data[Period] <= _currentperiod ) VAR _newtabletwo = FILTER ( ALL ( Data ), LEFT ( Data[Fiscal Year], 4 ) * 1 = _currentFYone - 1 && Data[Period] > _currentperiod ) VAR _unionnewtables = UNION ( _newtableone, _newtabletwo ) RETURN IF ( HASONEVALUE ( Data[Period] ), SUMX ( _unionnewtables, Data[Incident Count] ) / 13 )

Re: Moving Average - not date based

tamerj1 — Mon, 14 Nov 2022 18:54:28 GMT

Hi dantheram

you can create a calculated column for fiscal year rank

Fiscal Period Rank = RANKX ( 'Date', VALUE ( LEFT ( 'Date'[Fiscal Year], 4 ) ) * 100 + 'Date'[Period], , asc, DENSE )

then the measure would be

Incident Count MA = 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 ) )

Re: Moving Average - not date based

dantheram — Mon, 14 Nov 2022 19:13:26 GMT

thanks all - i'll be trying some of these tomrrow

Dan

Re: Moving Average - not date based

dantheram — Mon, 05 Dec 2022 18:27:35 GMT

this one works fine but does not recalculate when i add in some of the lower level categories.

For example, my 'incidents' field can be split by geography or a sub category - how do i make the calculation dynamic?

thanks

Re: Moving Average - not date based

Jihwan_Kim — Mon, 05 Dec 2022 18:35:01 GMT

Hi,

Please provide sample pbix file's link and then I can try to look into it to come up with a solution.

Thanks.

Re: Moving Average - not date based

dantheram — Mon, 05 Dec 2022 18:41:24 GMT

i cant see an option to upload the file, here is a link -

Derby SAFs.pbix

Re: Moving Average - not date based

dantheram — Mon, 05 Dec 2022 18:53:50 GMT

this one fails at the rank stage -

"a single value for column 'financial year in table 'SAFs Actuals' cannot be determined. this can happen when a measure formula refers to...."

any ideas?

Re: Moving Average - not date based

tamerj1 — Mon, 05 Dec 2022 18:56:08 GMT

dantheram

This is supposed to be a calculated column not a measure. Please read my answer above carefully

Re: Moving Average - not date based

dantheram — Mon, 05 Dec 2022 21:11:02 GMT

apologies - i've added to the file as a calc'd column and it works but the same problem observed with the other solution offered here - no recalcualtion when i add in other categories; i.e. if i split the incidents counts down by geography the MAA stays based on the total

thanks

Dan

Re: Moving Average - not date based

tamerj1 — Mon, 05 Dec 2022 21:13:42 GMT

dantheram
Also please note that a Date table is required for this solution to work properly.

Re: Moving Average - not date based

dantheram — Mon, 05 Dec 2022 21:17:10 GMT

so is the rank added to the 'date' table or the 'incident count' data table?

Re: Moving Average - not date based

tamerj1 — Mon, 05 Dec 2022 21:40:52 GMT

dantheram
The Date table

Re: Moving Average - not date based

dantheram — Mon, 05 Dec 2022 22:07:59 GMT

done - but its simply returing the period value >>>>

Re: Moving Average - not date based

Jihwan_Kim — Tue, 06 Dec 2022 04:03:19 GMT

Hi,

I am not sure but I think it is asking to login. If it is OK with you, could you please share it via onedrive, googledrive, or dropbox?

Thanks.

Re: Moving Average - not date based

tamerj1 — Tue, 06 Dec 2022 05:10:33 GMT

dantheram
The [Financial Year] and [Period] are from which table?

Re: Moving Average - not date based

dantheram — Tue, 06 Dec 2022 10:38:36 GMT

correct - i had used the wrong date table

just need to tidy up the output now; how do i stop it starting at 600 and running on past period 5 (the last period with data, so the year to date in effect)?

Re: Moving Average - not date based

tamerj1 — Tue, 06 Dec 2022 10:44:01 GMT

dantheram
Please clarify further

Re: Moving Average - not date based

dantheram — Tue, 06 Dec 2022 10:46:59 GMT

hi Tamer

the main issue is the calc running on past the year to date row - period 5, so it's picking up all the 0's and reducing the MA value, i need it to stop at the max value for period in the incident count dataset - so period 5