Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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,
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,
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |