Forum Discussion
Creating a moving average for count data ... but counting up a string column
Hello,
I'm trying to create a 7 day moving average for the count of activities for each date (with Activity as the legend).
I tried using the solution from this youtube tutorial video and other forum posts but I don't actually have a real "counts" column.
However, my biggest drawback and headache is the lack of a real "counts" column ... I could only make a Count Measure but I can't use it in the formula without getting some "MAX can't be used" or "cannot use string column" error.
SMA (7 day) =
AVERAGEX(
DATESBETWEEN(ActivityLogs[RetrieveDate],Max(ActivityLogs[RetrieveDate]) - 8, MAX(ActivityLogs[RetrieveDate])),
CALCULATE(SUM(ActivityLogs[Activity]))
)
Note that in the formula above, I think Calculate(SUM...) can only be used with a non-text Column, but I don't exactly have that in my table. Even if I set the summarize to "Count", or create a new measure, it won't work.
I even tried to table a new table by just counting the Activity instead, but I got stuck doing that as well.
If there's another way to do this I'm all ears.
Any suggestions? Thanks!
Table = ActivityLogs
| Date | Activity |
| 1-Jan | Create |
| 1-Jan | View |
| 1-Jan | View |
| 1-Jan | Delete |
| 2-Jan | Create |
| 2-Jan | Create |
| 2-Jan | Create |
| 3-Jan | View |
| 9-Jan | View |
| 10-Jan | View |
| 11-Jan | View |
etc ... hundred thousands of more rows, including other column metrics such as user name, location, etc.
Can't you just use COUNT inside of CALCULATE?
Note that you can't use DATESBETWEEN unless you have a proper date dimension table set up, so you might have to use a different approach like this:
SMA (7 day) = VAR CurrDate = MAX ( ActivityLogs[RetrieveDate] ) VAR Days = CALCULATETABLE ( VALUES ( ActivityLogs[RetrieveDate] ), ActivityLogs[RetrieveDate] <= CurrDate, ActivityLogs[RetrieveDate] > CurrDate - 7 ) RETURN AVERAGEX ( Days, CALCULATE ( COUNT ( ActivityLogs[Activity] ) ) )
6 Replies
Can't you just use COUNT inside of CALCULATE?
Note that you can't use DATESBETWEEN unless you have a proper date dimension table set up, so you might have to use a different approach like this:
SMA (7 day) = VAR CurrDate = MAX ( ActivityLogs[RetrieveDate] ) VAR Days = CALCULATETABLE ( VALUES ( ActivityLogs[RetrieveDate] ), ActivityLogs[RetrieveDate] <= CurrDate, ActivityLogs[RetrieveDate] > CurrDate - 7 ) RETURN AVERAGEX ( Days, CALCULATE ( COUNT ( ActivityLogs[Activity] ) ) )This actually works so well! Against a count of all activity, you can really see the comparison between the 7 day average and daily counts.
Unfortunately, I ran into another error: I have the Activity grouped in another column:Activities_Grouped = SWITCH( TRUE(),CONTAINSSTRING(ActivityLogs[Activity], "View") = TRUE, "View",CONTAINSSTRING(ActivityLogs[Activity], "Create") = TRUE, "Create" etc ... etc .... Around 15 of these.
So when I used this column as the legend, I get an error: "Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.. The exception was raised by the IDbCommand interface."
A web search indicated I needed to simplify the formula ... but is this even possible?
Thank you!This sounds like an issue with a calculated column being too complex for a DirectQuery, which is an entirely different question. Ideally, you could add that column at the data source rather than with DAX. I can't really think of a workaround to simplify a column definition like that.
First things first, is your Date column in an actual date format, or is it text with values like "1-Jan"?
It's an actual date column! Format is m/d/yyyy in PowerBI.
