[Help with moving Average 3 months from 2 years]

[bolabuga]

Hello everyone, 

First let me share a link of the sample file that i need help with the moving average.

https://drive.google.com/open?id=0B8Aq8DhGApJqYkpxVzdITzlMRkE

I have 1 calendar created for relation with the sample file. Its a custom caledar because the yearmonths dont have fix range of days. But that can be seen in the calendar on the pbix file shared in the link above.

I have yearmonths from jan/2016 up to mar/2017 and i would like to have a measure for calculating moving average of 3 months, but considering 3 months from the newest year and more 3 months from the sameperiod last year 3 months.

example: on fev/2017 i would like to have the sum of all sales amount from jan/2017, fev/2017 and mar/2017 plus jan/2016, fev/2016 and mar/2016, all divided by 6. Thus having the average of 6 months, 3 months of each year on the same period.

I could do the moving average of 3 months on sequential range, using the measures:

SALES AMOUNT = SUM('SAMPLE'[SALES])

MOVING AVERAGE 3 MONTHS = 
IF(
	ISBLANK(
		PREVIOUSMONTH('CALENDAR'[REFERSAMPLE])); 
	AVERAGEX(
		DATESINPERIOD(
			'CALENDAR'[REFERSAMPLE];
			PREVIOUSMONTH('CALENDAR'[REFERSAMPLE]);
			1;MONTH);					
		[SALES AMOUNT]);
	    AVERAGEX(
			DATESINPERIOD(
				'CALENDAR'[REFERSAMPLE];
				PREVIOUSMONTH('CALENDAR'[REFERSAMPLE]);
				2;MONTH);					
			[SALES AMOUNT]))

Wich can produce the report view:

[v-sihou-msft]

@bolabuga

I have downloaded your sample file. To calculate the moving average for past three months, you can create a measure like below:

Moving 3 Months Average = 
CALCULATE (
    AVERAGEX ( 'SAMPLE', 'SAMPLE'[REVENUE] ),
    DATESINPERIOD ( 'SAMPLE'[FullDate], LASTDATE ( 'SAMPLE'[FullDate] ), -3, MONTH )
)

To calculate moving 3 months average for same month in last year, please use formula below:

Moving 3 Months Average Last Year = 
VAR samedaylastyear =
    DATE ( YEAR ( LASTDATE ( 'SAMPLE'[FullDate] ) ) - 1, MONTH ( LASTDATE ( 'SAMPLE'[FullDate] ) ), 1 )
RETURN
    CALCULATE (
        AVERAGEX ( 'SAMPLE', 'SAMPLE'[REVENUE] ),
        DATESINPERIOD ( 'SAMPLE'[FullDate], samedaylastyear, -3, MONTH )
    )

Regards,

[Anonymous]

Hi, I have tried replicating this formula you wrote and i get a different answer,is there something wrong i did maybe? 

3 month avg = CALCULATE(AVERAGEX(Dim_Brokerage;Dim_Brokerage[Brokerage]);DATESINPERIOD(Dim_Date[Date];LASTDATE(Dim_Date[Date]);-3;MONTH))

[bolabuga]

As i understand looking at your measure, you will always get result of an average of the "last" 3 months of your date column.

LASTDATE will get your non blank last date and from there "-3" months. I dont know which context you are using this measure, if its the simplest context possible, lets say a matrix table with the column client on "row" and this measure as result, you will be seeing the average "brokerage" for each client of the last 3 months "from" the lastdate, and in case the client has no results in that period, the result will be blank.

At the grandtotal you will be seeing the average of the results of all clients of the last 3 months from the lastdate.

[Anonymous]

Thank you for the help,I created this formula and it displays the 3 months average as requested, but when displayed on a matrix table it returns blank spaces,

3 month avg = CALCULATE(AVERAGEX(SUMMARIZE(Dim_Date;Dim_Date[Date].[Year];Dim_Date[Month];"3 month avg";SUM(Dim_Brokerage[Brokerage]));[3 month avg]);DATESINPERIOD(Dim_Date[Date].[Date];LASTDATE(Dim_Date[Date].[Date]);-3;MONTH))

[bolabuga · Accepted solution]

@v-sihou-msft

I manage to get a solution witht the help of "Definitive guide to DAX" Book.

Im using on the calendar table a calculated column named month sequential number (MSEQNUMBER), that will be used to filter the periods that want to use on the moving average result.

MSEQNUMBER = 
IF(
    'CALENDAR'[Date] >= MIN('SAMPLE'[DATE]) && 'CALENDAR'[Date] <= DATE(2016;2;10);
    YEAR('CALENDAR'[Date])*12 + 1 - 1;
    IF(
        'CALENDAR'[Date] >= DATE(2016;2;11) && 'CALENDAR'[Date] <= DATE(2016;3;10);
        YEAR('CALENDAR'[Date])*12 + 2 - 1;
        IF(
            'CALENDAR'[Date] >= DATE(2016;3;11) && 'CALENDAR'[Date] <= DATE(2016;4;13);
            YEAR('CALENDAR'[Date])*12 + 3 - 1;
            IF(
                'CALENDAR'[Date] >= DATE(2016;4;14) && 'CALENDAR'[Date] <= DATE(2016;5;9);
                YEAR('CALENDAR'[Date])*12 + 4 - 1;
                IF(
                    'CALENDAR'[Date] >= DATE(2016;5;10) && 'CALENDAR'[Date] <= DATE(2016;6;10);
                    YEAR('CALENDAR'[Date])*12 + 5 - 1;
                    IF(
                        'CALENDAR'[Date] >= DATE(2016;6;11) && 'CALENDAR'[Date] <= DATE(2016;7;11);
                        YEAR('CALENDAR'[Date])*12 + 6 - 1;
                        IF(
                            'CALENDAR'[Date] >= DATE(2016;7;12) && 'CALENDAR'[Date] <= DATE(2016;8;9);
                            YEAR('CALENDAR'[Date])*12 + 7 - 1;
                            IF(
                                'CALENDAR'[Date] >= DATE(2016;8;10) && 'CALENDAR'[Date] <= DATE(2016;9;11);
                                YEAR('CALENDAR'[Date])*12 + 8 - 1;
                                IF(
                                    'CALENDAR'[Date] >= DATE(2016;9;12) && 'CALENDAR'[Date] <= DATE(2016;10;13);
                                    YEAR('CALENDAR'[Date])*12 + 9 - 1;
                                    IF(
                                        'CALENDAR'[Date] >= DATE(2016;10;14) && 'CALENDAR'[Date] <= DATE(2016;11;11);
                                        YEAR('CALENDAR'[Date])*12 + 10 - 1;
                                        IF(
                                            'CALENDAR'[Date] >= DATE(2016;11;12) && 'CALENDAR'[Date] <= DATE(2016;12;11);
                                            YEAR('CALENDAR'[Date])*12 + 11 - 1;
                                            IF(
                                                'CALENDAR'[Date] >= DATE(2016;12;12) && 'CALENDAR'[Date] <= DATE(2017;1;9);
                                                2016*12 + 12 - 1;
                                                IF(
                                                    'CALENDAR'[Date] >= DATE(2017;1;10) && 'CALENDAR'[Date] <= DATE(2017;2;17);
                                                    YEAR('CALENDAR'[Date])*12 + 1 - 1;
                                                    IF(
                                                        'CALENDAR'[Date] >= DATE(2017;2;18) && 'CALENDAR'[Date] <= DATE(2017;3;8);
                                                        YEAR('CALENDAR'[Date])*12 + 2 - 1;
                                                        IF(
                                                            'CALENDAR'[Date] >= DATE(2017;3;9) && 'CALENDAR'[Date] <= DATE(2017;4;13);
                                                            YEAR('CALENDAR'[Date])*12 + 3 - 1)))))))))))))))

Having the MSEQNUMBER on the calendar table, allowed me to write the following measures:

SALES AMOUNT AVERAGE = 
AVERAGEX(
	VALUES('CALENDAR'[REFERSAMPLE]);
	[SALES AMOUNT])

MOVING AVER 3MONTHS NEW = 
SUMX(
	VALUES('CALENDAR'[MSEQNUMBER]);
	CALCULATE(
		'SAMPLE'[SALES AMOUNT AVERAGE];
		ALL('CALENDAR');
		FILTER(
			ALL('CALENDAR'[MSEQNUMBER]);
			'CALENDAR'[MSEQNUMBER] >= EARLIER('CALENDAR'[MSEQNUMBER]) - 1
			&& 'CALENDAR'[MSEQNUMBER] <= EARLIER('CALENDAR'[MSEQNUMBER]) + 1)))

MOVING AVERAGE 3MONTHS 2YEARS = 
SUMX(
	VALUES('CALENDAR'[MSEQNUMBER]);
	CALCULATE(
		[SALES AMOUNT AVERAGE];
		ALL('CALENDAR');
		FILTER(
			ALL('CALENDAR'[MSEQNUMBER]);
        	        ('CALENDAR'[MSEQNUMBER] >= EARLIER('CALENDAR'[MSEQNUMBER]) -1
        	        && 'CALENDAR'[MSEQNUMBER] <= EARLIER('CALENDAR'[MSEQNUMBER]) + 1)
        	        ||
        	        ('CALENDAR'[MSEQNUMBER] >= EARLIER('CALENDAR'[MSEQNUMBER]) -13
        	        && 'CALENDAR'[MSEQNUMBER] <= EARLIER('CALENDAR'[MSEQNUMBER]) -11))))

The result can be seen at the .pbix file on:

https://drive.google.com/open?id=0B8Aq8DhGApJqal9XcmNuMENvVGs

[bolabuga]

@v-sihou-msft

First thanks for the help.

I didnt know i could use var and datesinperiod in that way, really cool to learn.

But that was not what i need. i dont want the moving average in 2 results from 2 measures, and the moving average range must be for example for month 2:

month >= month -1 

and
month <= month + 1,

Getting me results from month 1 to month 3. (also the result must be average against months level, not day). 

for month 02/2017 for example i need sum of 01,02 and 03 of 2017 plus sum of 01,02 and 03 of 2016, and then divide the total result by 6. And so on for the moving average.