Forum Discussion

shinney's avatar
shinney
Helper I
4 years ago
Solved

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 for...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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] ) ) )
AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

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's avatar
shinney
Helper I
4 years ago

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.