Moving average over non-numeric values

[jlum]

Hi, I'm trying to create a measure for calculating a 3 day moving average of unique events of string type.

On the dashboard there is a timeline slicer for date, so the user can visualize the dashboard data by day, month or quarter. I want to add a 3 day moving average line in the events bar chart. The 3 day moving average is independent of the slicer settings. How can I use a new measure to calculate the 3 day moving average with a DAX formula?

Below a simplified example table:

Date, Events

4/26/2017, Event A

4/26/2017, Event A 

4/26/2017, Event B

4/26/2017, Event C

4/26/2017, Event D

4/27/2017, Event A 

4/27/2017, Event B

4/27/2017, Event C

4/28/2017, Event A

4/28/2017, Event A 

4/28/2017, Event B

4/28/2017, Event C

4/28/2017, Event D

4/29/2017, Event B

4/29/2017, Event B

4/30/2017, Event A

4/30/2017, Event B 

4/30/2017, Event C

The expected result are as follows.

The number of unique events for 4/26/2017=4 (Events A, B, C, D), 4/27/2017=3, 4/28/2017=4, 4/29/2017=1, etc.

The 3 day moving average on 4/28/2017 = (4+3+4)/3=3.67 events, on 4/29/2017=(3+4+1)/3=2.67 events.

I tried the following but it did not return the desired result:

3_Day_Moving_Average = CALCULATE(AVERAGEX(Table, DISTINCTCOUNT(Table[Events])), 
DATESINPERIOD(Table[Date], LASTDATE(Table[Date]), -3, DAY))

Thanks.

[v-sihou-msft · Accepted solution]

@jlum

In this scenario, since you need to get the distinct count of event on each day and calculate average, I suggest you summarize a new table to with distict count calculated group on day level.

Daily Events=
SUMMARIZE (
    Table,
    Table[Date],
    "Daily Distinct Count Events", CALCULATE ( DISTINCTCOUNT ( Table[Events] ), ALLEXCEPT ( Table, Table[Date] ) )
)

Then you can calculate the moving average based on above calculated table.

Regards,

[hema_cherupalli]

Hi @v-sihou-msft

I also bumped into the same issue of  calculating the "Moving average over non-numeric values"

And tried your solution but getting the following error:

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

I wonder what does this actually mean?

Any help is much appreciated.

Thanks

[jlum]

@v-sihou-msft

I was hoping there was a way to calculate the moving average without creating a new table.

I ended up doing what you suggested.

Thanks.