Dax Rolling Average with multiple records per day

Question

Take a simple table

SalesTime
Product
UnitsSold

There is one row per sale. So there are multiple rows per day. I need a chart that will show the average units sold per sale over 7 days and average units sold per day over 7 days.

The examples that I found all used DATESBETWEEN or DATESINPERIOD and those throw an error if the table has multiple records per date.

Are you looking to write a calculated column that has these averages or do you want write a measure to drop into a visual? — Alexis Olson
I was assuming a Measure to drop into a visual or matrix/pivot. I didn't know that PowerBI columns could have aggregate calculations like this that spanned rows. — Rabbi

Alexis Olson Alexis Olson · Accepted Answer · 2017-12-06T22:59:23

I will name this table Sales and assume that Sales[SalesTime] is a date type rather than a datetime type. If not, create a new calculated column

Sales[SalesDate] = Sales[SalesTime].[Date]

and work with that instead.

Your rolling average units per sale can be calculated something like this:

AvgUnitsPerSale = 
VAR CurrDay = MIN(Sales[SalesTime])
RETURN CALCULATE(
            AVERAGE(Sales[UnitsSold]),
            DATESBETWEEN(Sales[SalesTime], CurrDay-7, CurrDay))

You can get an average count of sales per day by using COUNT instead of AVERAGE. To get the average units sold per day, multiply the average count of sales and the average units per sale.

Dax Rolling Average with multiple records per day

1 Answers