Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Visulisation issue when using 6 month rolling average measure and relative date slicer

Hi, I'm looking for some help with one of my reports.  I have some bar and line graphs that use a 6 Month Rolling Average measure for the line graph.  Due to the way my data is set up there are entries up to the end of 2024, however to focus on the most recent values that have occurred within the last 6 months I then put a relative date slicer on the visualisation for the 'Last 6 Months'.

 

However when doing this December 2023 is present on my visualisation, which clearly isn't correct.

 

 

In trying to understand why this was happening I found that if I removed the 6 Month Rolling Average measure the graph showed the true last 6 Months.  

 

I also checked if smaller figures changed the outlook and worked out that if I set the relative date slicer to 'the last 9 weeks' December does not appear.  Since this check was carried out in Week 10 of 2023 I can only assume that when I use the 6 month rolling average measure to look back and the time period is over the change in calendar year that this is causing an issue?  (please note that this was not happening last year)

 

Does anyone have any ideas how to sort this?

 

I've anomolised the above graph but can't post any files.  I have a date table linked to my input data, and my 6 month rolling average measure is doing the following:

 

6 Month Rolling Average = VAR TimeM = 6
VAR RollingSold =
CALCULATE(([Measures totalling the number of different jumpers sold]),
DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]), -TimeM, MONTH)
)
VAR RollingReturn =
CALCULATE(([Measures totalling the number of different jumpers returned]),
DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-TimeM,MONTH)
)
Return
(1 - RollingRej/RollingSum) * 100
 
Any help would be appreciated.

 

 

5 Replies

  • Hi,

    Difficult to help without seeing your file but try this

    Measure = if(eomonth(today(),0)<min(Date[date]),blank(),[your masure])

    If this does not work, then i will need the PBI file.  If you are OK, then share the dowload link.

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Ashish,

     

    Thanks for getting back to me, I can't share my original file but I recreated a simpler version which less data but in the same structure/approach.

     

    https://we.tl/t-oZsSztuH6R 

     

     

      • Anonymous's avatar
        Anonymous
        Not applicable

        Thanks Ashish, much appreciated.