Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
9 | |
7 |