Average Aggregation Problem

[iamprajot]

Hi

I was testing an Sample Data for 2 Months Moving Average with Blanks in it (Blanks is not the Problem).

The problem is, if we create a Pivot Table from this Data and Sum 2 Months Total then we get the result what is required but what Power BI does is, it gives the Average but it Divides the 2 Months Total with the number of transactions in the Actual Data.

for e.g. Pivot does Jan+Feb/2 but Power BI does Jan+Jan+Feb/3 because of the 2 Transactions in the Jan

here the result from Pivot Table:-

Year	Month	Total	2 Months Moving Average
2016	Jan	999.5	999.50
2016	Feb	719.37	859.44
2016	Mar	167.44	443.41
2016	Apr	448.65	308.05

And here is the result from Power BI:-

Year	Month	Total Amount	2 Months Moving Average
2016	Jan	999.5	999.50
2016	Feb	719.37	572.96
2016	Mar	167.44	295.60
2016	Apr	448.65	205.36

What Power BI did is:-

 

Please test this data in Excel and Power BI both.

I started achiving below but hit a wall because of above things for which I need answers too.

What I was looking for is,

I need to create an Inner Summarized Table using SUMMARIZE which contains Year, Month, Month Total and 2 Months Moving Average from which I was planning to take the Month Total to get the Average of 2 Months Moving

=
ADDCOLUMNS (
SUMMARIZE (
Data,
Data[OrderDate].[Year],
Data[OrderDate].[MonthNo],
"Final Month Total", [Total Amount]
),
"2 Months Moving Average", AVERAGEX (
DATESINPERIOD (
Data[OrderDate].[Date],
LASTDATE ( Data[OrderDate].[Date] ),
-2,
MONTH
),
[Final Total]
)
)

Total Amount is an Measure, SUM(Data[Total])

Data is here...

OrderDate	Year	Month	Region	Rep	Item	Units	Unit Cost	Total
1/6/16	2016	Jan	East	Jones	Pencil			0
1/23/16	2016	Jan	Central	Kivell	Binder	50	19.99	999.5
2/9/16	2016	Feb	Central	Jardine	Pencil	36	4.99	179.64
2/26/16	2016	Feb	Central	Gill	Pen	27	19.99	539.73
3/15/16	2016	Mar	West	Sorvino	Pencil	56	2.99	167.44
4/1/16	2016	Apr	East	Jones	Binder	60	4.99	299.4
4/18/16	2016	Apr	Central	Andrews	Pencil	75	1.99	149.25
5/5/16	2016	May	Central	Jardine	Pencil	90	4.99	449.1
5/22/16	2016	May	West	Thompson	Pencil	32	1.99	63.68
6/8/16	2016	Jun	East	Jones	Binder	60	8.99	539.4
6/25/16	2016	Jun	Central	Morgan	Pencil	90	4.99	449.1
7/12/16	2016	Jul	East	Howard	Binder	29	1.99	57.71
7/29/16	2016	Jul	East	Parent	Binder	81	19.99	1619.19
8/15/16	2016	Aug	East	Jones	Pencil	35	4.99	174.65
9/1/16	2016	Sep	Central	Smith	Desk			0
9/18/16	2016	Sep	East	Jones	Pen Set	16	15.99	255.84
10/5/16	2016	Oct	Central	Morgan	Binder	28	8.99	251.72
10/22/16	2016	Oct	East	Jones	Pen	64	8.99	575.36
11/8/16	2016	Nov	East	Parent	Pen	15	19.99	299.85
11/25/16	2016	Nov	Central	Kivell	Pen Set			0
12/12/16	2016	Dec	Central	Smith	Pencil	67	1.29	86.43
12/29/16	2016	Dec	East	Parent	Pen Set	74	15.99	1183.26
1/15/17	2017	Jan	Central	Gill	Binder	46	8.99	413.54
2/1/17	2017	Feb	Central	Smith	Binder	87	15	1305
2/18/17	2017	Feb	East	Jones	Binder			0
3/7/17	2017	Mar	West	Sorvino	Binder			0
3/24/17	2017	Mar	Central	Jardine	Pen Set	50	4.99	249.5
4/10/17	2017	Apr	Central	Andrews	Pencil	66	1.99	131.34
4/27/17	2017	Apr	East	Howard	Pen			0
5/14/17	2017	May	Central	Gill	Pencil	53	1.29	68.37
5/31/17	2017	May	Central	Gill	Binder	80	8.99	719.2
6/17/17	2017	Jun	Central	Kivell	Desk			0
7/4/17	2017	Jul	East	Jones	Pen Set	62	4.99	309.38
7/21/17	2017	Jul	Central	Morgan	Pen Set	55	12.49	686.95
8/7/17	2017	Aug	Central	Kivell	Pen Set	42	23.95	1005.9
8/24/17	2017	Aug	West	Sorvino	Desk			0
9/10/17	2017	Sep	Central	Gill	Pencil			0
9/27/17	2017	Sep	West	Sorvino	Pen	76	1.99	151.24
10/14/17	2017	Oct	West	Thompson	Binder	57	19.99	1139.43
10/31/17	2017	Oct	Central	Andrews	Pencil	14	1.29	18.06
11/17/17	2017	Nov	Central	Jardine	Binder	11	4.99	54.89
12/4/17	2017	Dec	Central	Jardine	Binder			0
12/21/17	2017	Dec	Central	Andrews	Binder	28	4.99	139.72

[v-piga-msft · Accepted solution]

Hi @iamprajot,

In Power BI, you also could get the result as you get in Excel. 

However you need to modify your formula like below to get the moving average by 2 month.

Before you create the measure, please create a canlendar table and create the relationships between the two tables.

Calendar table = CALENDARAUTO()

Moving_Average_2_Months =
CALCULATE (
    AVERAGEX (
        SUMMARIZE (
            'Data',
            'Calendar table'[Date].[Year],
            'Calendar table'[Date].[Month],
            "sumtotal", SUM ( Data[Total] )
        ),
        [sumtotal]
    ),
    DATESINPERIOD (
        'Calendar table'[Date],
        LASTDATE ( 'Calendar table'[Date] ),
        -2,
        MONTH
    )
)

Then you will get the output below.

Hope this can help you!

Best Regards,

Cherry

[iamprajot]

This is exactly what I was looking for. Thanks for going through the whole explanation.

Although it is working without the Calendar Table aswell.