Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I am looking to plot the google mobility data for a few countries (line with Date on axis and Value1 in value, the legend is Country_Region), so i have something looking like the image below. What I want to do it create a rolling 7-day median filter that will remove the spikes caused by holidays etc. ... Can this be done via DAX?
Thanks in advance,
Andrew
@andcol ,
example
Rolling 7 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-7,DAY)) /7
Rolling 7 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-7,DAY))/7
or divide distinct count of days
or
Rolling 7 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-7,DAY))
Rolling 7 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-7,DAY))
Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.
Thanks. Can a median filter, rather than a 7-day average be incorporated, I basically want to exclude these spikes rather than average them out and drag down surrounding points. Thanks
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
81 | |
55 | |
39 | |
20 | |
12 |