Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculating Average Monthly Quantity

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 th...
  • bcdobbs's avatar
    4 years ago

    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: