Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rsbin
Super User
Super User

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
Super User

@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?

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

@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?

@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

 

@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])))

@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,

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.