Calculate average sku sold per month

[RVBIBS]

I have tried to search for other topics regarding this problem, but can't seem to get any of them to work.

I want to find the average amount of units sold per month per SKU in my DATA table.

This is my table

And this is some of the data

Do anyone has a smart way to do this?

[FreemanZ]

hi @RVBIBS 

try to

1) add column with this:

YYMM = FORMAT([Date], "YYYYMM")

2) write a measure like this:

AvgByMonthBySKU =

VAR _table =

SUMMARIZE(

    data,

    data[YYMM],

    data[SKU]

)

RETURN

DIVIDE(

    SUM(data[Sales]),

    COUNTROWS(_table)

)

i tried to verify with a simplified sample, it worked like this:

[johnt75]

It is safest to build a star schema rather than one flat table. You want to add a proper date table, marked as a date table, and you should also have a separate table for your SKUs, which you could create like

SKU Table = SUMMARIZE('Table', 'Table'[SKU], 'Table'[Item name])

Link both the SKU table and the date table in one-to-many relationships to your fact table, and always use  columns from these tables on your visuals and in filters and slicers.

You can then create a measure to show the average quantity like

Avg Quantity = AVERAGE('Table'[Quantity])

You can put this measure into visuals with columns from your SKU and date tables and it will show you the averages broken down by whatever you choose.