Forum Discussion
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...
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] ) ) )
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.
