cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rsbin
Post Partisan
Post Partisan

Moving Average in Tooltip Page - Date Filter Context?

Good Afternoon,

 

I am working with Moving Average calculations.  In my time series charts, everything works as it should.

image.png

I am creating a custom Tooltip Page showing data for each date.  My issue is with the Date Context.  When I hover over a specific data point, I believe it is only using the current date context and hence does not display the correct result.  The correct value for the 3 Day Moving Average for this date is 0.23.  However, what I believe is happening is the tooltip is "filtered" for a single data point, and hence it is throwing back the actual result for that single day.  Appears I am losing the context of the previous 2 days.

 

image.png

 

 

 

 

 

I am using the Advanced Card Visual for my Tooltip Page, but am flexible with this choice.  Hoping someone may have a suggestion to get around this issue.

 

Thanks in advance and Best Regards to All,

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@rsbin , are using a formula like

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-3,Day))/3

 

with the date table?



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

7 REPLIES 7
amitchandak
Super User IV
Super User IV

@rsbin , are using a formula like

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-3,Day))/3

 

with the date table?



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

@amitchandak 

I am using a combination of 3 formulas.  Tried several different variations from the Forum and found this works best.

3DayEquipID = 
Var _Length = 3
Var _CurrentIndex = MAX('GeneralStatistics'[GSIndex])
Var _PrevIndex = _CurrentIndex - _Length
RETURN
If (_CurrentIndex >= _Length, 
   Sumx(Filter(ALLSELECTED('GeneralStatistics'),
               _CurrentIndex >= 'GeneralStatistics'[GSIndex] && _PrevIndex < 'GeneralStatistics'[GSIndex]),
               'Work Item Measures'[EquipmentIDCount]))

Used a simlar one to calculate 3Day Visits, then using this:

EquipID3DayMA = [3DayEquipID]/[3DayVisits]

I do have a Calendar Table linked to my GeneralStatistics table.

@rsbin , As your tooltip, is getting only 1 date, your allselected will not go beyond that

add removefilters('GeneralStatistics'[GSIndex])

 

3DayEquipID = 
Var _Length = 3
Var _CurrentIndex = MAX('GeneralStatistics'[GSIndex])
Var _PrevIndex = _CurrentIndex - _Length
RETURN
If (_CurrentIndex >= _Length, 
   Sumx(Filter(ALLSELECTED('GeneralStatistics'),
               _CurrentIndex >= 'GeneralStatistics'[GSIndex] && _PrevIndex < 'GeneralStatistics'[GSIndex]),
               'Work Item Measures'[EquipmentIDCount]), removefilters('GeneralStatistics'[GSIndex]))

 

https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak 

Thanks much for the suggestion.  However, am getting the following error message:

rsbin_0-1595011843984.png

Thinking it doesn't like the SUMX???

@rsbin , try like

3DayEquipID = 
Var _Length = 3
Var _CurrentIndex = MAX('GeneralStatistics'[GSIndex])
Var _PrevIndex = _CurrentIndex - _Length
RETURN
If (_CurrentIndex >= _Length, 
   calculate(Sumx(Filter(ALLSELECTED('GeneralStatistics'),
               _CurrentIndex >= 'GeneralStatistics'[GSIndex] && _PrevIndex < 'GeneralStatistics'[GSIndex]),
               'Work Item Measures'[EquipmentIDCount]), removefilters('GeneralStatistics'[GSIndex])))


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak 

Proving to be a little bit of a challenge.  Really appreciate your efforts with this one.

Your last correction solved the error, so thanks for that.  Unfortunately, the behaviour is still the same when I test it in the Tooltip.  It seems to be only calculating based on the single day.  Below is a sample data set.

It's almost like I have to force it to do a calculation for the selected day + the 2 prior days.

DateEquipmentIDCountTotalVisitsEquipmentIDRatio3DayEquipID A3DayVisits3DMA_EquipID
8/1/20193085135550.228   
8/2/20192936135240.217   
8/3/2019103749970.2087058320760.22
8/4/201963733260.1924610218470.211
8/5/2019232886190.274002169420.236
8/6/20192914145990.25879265440.221
8/7/20193002143830.2098244376010.219
8/8/20193077141920.2178993431740.208
8/9/20193088139610.2219167425360.216
8/10/2019155163660.2447716345190.224
8/11/201995339990.2385592243260.23
8/12/20192560129690.1975064233340.217
8/13/20192715142890.196228312570.199
8/14/20193171139220.2288446411800.205

image.png

 

 

 

 

 

 

 

 

 

The Blue Line is EquipmentID Ratio and Red Line is the 3 Day MA.

As you can see, Tooltip seems to be considering only 1 day.  Any further suggestions are of course much appreciated.

Best Regards,

rsbin
Post Partisan
Post Partisan

@amitchandak 

After more testing and trial and error, I think I can get this to work properly by using your original Moving Average formula, albeit in a modified form.  One of the reasons I went with using the [GSIndex] is that it ensures there are at least 3 data points before calculating the first Moving Average.  Using your format in a quick test, I get the following:

rsbin_0-1595022093094.png

By calculating the average first, rather than the sum....I think I can get to the right solution.

Thank you for getting me on the right track.  Kudos to you for your time.

 

Have a great weekend and Kindest Regards,

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors