Creating a moving average for count data ... but counting up a string column

[shinney]

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. 

[Gabriel_Walkman]

First things first, is your Date column in an actual date format, or is it text with values like "1-Jan"?

[shinney]

It's an actual date column! Format is m/d/yyyy in PowerBI. 

[AlexisOlson · Accepted solution]

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] ) ) )

[shinney]

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!

[AlexisOlson]

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.

[shinney]

Thank you very much for the insight! I didn't actually think of that. I'll ask my team if that column would be possible at the source.