Calculating Average Monthly Quantity

[Anonymous]

I've data that contains orders from customers along with quantities of each item.

I wish to calculate the monthly average of each material per customer. I tried to use the average function but the numbers didn't add up properly.

I'm trying to generate report similar to this one

Material#	Material Description	Customer	Average Monthly Quantity
123456	Pencils	Staples
123456	Pencils	Walmart
123456	Pencils	Costco
654789	Sharpner	Staples
654789	Sharpner	Walmart
654789	Sharpner	Costco
987456	Ink	Staples
987456	Ink	Walmart
987456	Ink	Costco

My Sample Data looks like this

Date	Material#	Material Description	Customer	Quantity
1/1/2021	123456	Pencils	Staples	100
1/1/2021	654789	Sharpner	Walmart	400
25/1/2021	654123	Paper	Costco	300
1/3/2021	123456	Pencils	Walmart	300
2/3/2021	987456	Ink	Staples	500
15/3/2021	654789	Sharpner	Costco	600
2/4/2021	123456	Pencils	Staples	100
10/4/2021	654123	Paper	Walmart	400
15/4/2021	987456	Ink	Staples	300
10/5/2021	987456	Ink	Costco	200
15/5/2021	123456	Pencils	Costco	500
20/5/2021	654789	Sharpner	Staples	100
7/7/2021	654123	Paper	Costco	200
8/7/2021	123456	Pencils	Costco	500
20/7/2021	987456	Ink	Walmart	400
2/8/2021	654789	Sharpner	Staples	300
24/08/2021	123456	Pencils	Walmart	100
15/9/2021	987456	Ink	Costco	200
20/10/2021	123456	Pencils	Staples	200
25/10/2021	654123	Paper	Costco	300
29/10/2021	123456	Pencils	Staples	200
17/11/2021	654789	Sharpner	Walmart	600
20/11/2021	987456	Ink	Walmart	200
27/11/2021	654789	Sharpner	Costco	400
10/12/2021	123456	Pencils	Walmart	400
15/12/2021	654123	Paper	Costco	300
16/12/2021	123456	Pencils	Walmart	200

[bcdobbs · Accepted solution]

Try the following:

1) Create a date table:

Date = 
    VAR EarliestYear = YEAR ( MIN ( Sales[Date] ) )
    VAR LatestYear = YEAR ( MAX ( Sales[Date] ) )

    RETURN
    ADDCOLUMNS (
        CALENDAR (
            DATE ( EarliestYear, 1, 1 ),
            DATE ( LatestYear, 31, 12 )
        ),
        "Month", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ) //Format as mmmm YYYY
    )

2) Set column types as date for both and format Month as mmmm YYYY

3) Mark it as a date table.

4) Create relationship to your main table (I called mine Sales)

5) Create a measure in the main table:

Average Monthly Quantity = 
    AVERAGEX (
        VALUES ( 'Date'[Month] ),
        CALCULATE ( SUM ( Sales[Quantity] ) )
    )

6) Use it in a table visual: