Forum Discussion
Anonymous
4 years agoNot applicable
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...
- 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:
bcdobbs
Community Champion
4 years agoTry 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: