Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
shinney
Helper I
Helper I

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

DateActivity
1-JanCreate
1-JanView
1-JanView
1-JanDelete
2-JanCreate
2-JanCreate
2-JanCreate
3-JanView
9-JanView
10-JanView
11-JanView

etc ... hundred thousands of more rows, including other column metrics such as user name, location, etc. 

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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

View solution in original post

6 REPLIES 6
Gabriel_Walkman
Continued Contributor
Continued Contributor

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. 

AlexisOlson
Super User
Super User

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.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.