I am attempting to create a measure that shows the 14-day average sales count over time by day. Given the sensitivity of the data I am handling, I cannot post that data here, but here is an analogous example of the sales of a generic product that models what I am trying to do:
| Sale Date | Brand | Color | Size |
| 01/01/2001 | A | Red | Small |
| 01/05/2001 | A | Blue | Medium |
| 01/05/2001 | A | Blue | Large |
| 01/14/2001 | B | Blue | Medium |
| 01/16/2001 | B | Red | Small |
| 01/17/2001 | A | Yellow | Large |
| 01/19/2001 | A | Blue | Large |
| 01/22/2001 | B | Blue | Medium |
| 01/24/2001 | A | Blue | Large |
| 01/24/2001 | A | Blue | Large |
| 01/27/2001 | B | Blue | Small |
| 01/28/2001 | B | Red | Large |
| 01/31/2001 | B | Yellow | Small |
I want to be able to count the average number of units sold per brand the last 14 days for every day in the date range. Further, there can be and often will be more than one sale on any given day in the dataset. My end goal is I want to be able to filter with slicers on different details. I may want to see a stacked line graph of the 14-day rolling average with brand sales broken down by brand in the legend and a slicer for color and size, and the average count must adjust according to the slicer selection. For example, if I set the slicer to blue only, it should show a rolling average count of sales for brand A of 0.21 and brand B of 0.07 units sold per day (given 3 and 1 sold respectively over 14 days, from 01/01/2001 through 01/14/2001).
When I attempt this in DAX like so:
14-Day Rolling Average Sales Count=
COUNTROWS (
FILTER (
'Product Sales',
'Product Sales'[Sale Date] <= 'Product Sales'[Sale Date]
&& 'Product Sales'[Sale Date] > DATEADD ( 'Product Sales'[Sale Date], -14, DAY )
)
) / 14
Expectedly, I get only the current day's sales count divided by 14. How can I indicate in DAX that I want to look to previous dates up to 14 days back on any given date and get the average daily sales count? Again, I want to be able to maintain live filterability of the average with slicers. That is why I try to let the outer filters apply in the next example.
I attempt this, but the count fails and the graph shows up as blank.
14-Day Rolling Average Sales Count =
CALCULATE (
COUNTROWS ( 'Product Sales' ),
KEEPFILTERS (
FILTER (
ALL ( 'Product Sales' ),
COUNTX (
KEEPFILTERS (
FILTER (
'Product Sales',
EARLIER ( 'Product Sales'[Sale Date] ) < 'Product Sales'[Sale Date]
&& EARLIER ( 'Product Sales'[Sale Date] ) >= 'Product Sales'[Sale Date] - 13
)
),
'Product Sales'[Brand]
)
)
)
) / 14
