DAX Rolling Average based on count of number of rows per day

Question

I have a table (TableA), one of the columns is called DateAdded which is a Date timestamp, the table has multiple rows entered into it per day. In PowerBI, I have set this table up with a relationship to a date table (Dates). If I add TableA to a simple list view and place a date range slicer on the page, all works as expected (TableA gets filtered down based on date selection) - this proves the relationship works.

What I would like to do is add a rolling average in a chart based off the number of rows added per day.

TableA

Date...............| Product...............

The expected output is

Date...............| Rolling Average...............

2021-07-10 | 3
2021-07-11 | 2.5
2021-07-12 | 2
2021-07-13 | 2.5

Here is my current DAX, but it is returning exactly the same as the regular count, as opposed to a rolling average.

Rolling Average = 
  AVERAGEX(
   FILTER( ALLSELECTED(Dates),
      Dates[Date] <= MAX(Dates[Date])),
      COUNT('TableA'[Product])
)

Where am I going wrong?

Thanks.

I am a bit puzzled to understand what you are looking for. You are talking about a rolling average but in the same sentence you are saying per day. Does this mean you want the average of the day, or you want the rolling average from the date selected till now? Please give us example of input date so we understand what you try to achive. — Aldert

smpa01 smpa01 · Accepted Answer · 2021-07-13T21:27:04

I assumed you have a DM like following

_date = MAX(_date[date])
_Count = COUNTROWS(_fact)
_rollingCount = 
VAR _1 = MAX(_date[date])
VAR _2 = CALCULATE(CALCULATE([_Count], FILTER(_date,_date[date]<=_1)),ALL(_date[date]))
RETURN _2
_rank = RANKX(ALLSELECTED(_date),[_date],,ASC)
_rollingAverage = DIVIDE([_rollingCount],[_rank])

DAX Rolling Average based on count of number of rows per day

1 Answers