Different Rolling Average Timing show the same result

[738o51]

 https://www.dropbox.com/s/zo33se3wtimyyzo/pbix%20for%20forum.pbix?dl=0

^.pbix

Hi all,

I am building a report that needs to show a graph with average census per weekday, overlayed with three rolling averages with different timeframes. You can see my attempt in the .pbix above. I also need these metrics to be filterable by census location and type.

My problem is that each of my averages is showing the same timeframe that is selected in the date slicer. I've used various attempts at all/allselected, but I end up with either rolling average that don't go beyond the slicer dates, or not responsive to weekdays.

Can some one please help?

Thanks!

[v-shex-msft · Accepted solution]

HI @738o51 ,

I'd like to suggest you apply all filter on ram table o ignore filter effect form calendar table:

1 Week Rolling Average = 
CALCULATE (
    AVERAGE ( CensusOcc[Census] ),
    FILTER (
        ALL ( 'CensusOcc' ),
        'CensusOcc'[Date] < MAX ( 'Date'[Date] )
            && 'CensusOcc'[Date]
                >= MAX ( 'Date'[Date] ) - 7
    )
)

2 Week Rolling Average = 
CALCULATE (
    AVERAGE ( CensusOcc[Census] ),
    FILTER (
        ALL ( 'CensusOcc' ),
        'CensusOcc'[Date] < MAX ( 'Date'[Date] )
            && 'CensusOcc'[Date]
                >= MAX ( 'Date'[Date] ) - 7*2
    )
)

3 Week Rolling Average = 
CALCULATE (
    AVERAGE ( CensusOcc[Census] ),
    FILTER (
        ALL ( 'CensusOcc' ),
        'CensusOcc'[Date] < MAX ( 'Date'[Date] )
            && 'CensusOcc'[Date]
                >= MAX ( 'Date'[Date] ) - 7*3
    )
)

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @738o51 ,

Please use Edit interactions option to stop the interaction between slicer and graph.

Click on Date Slicer, Go to Format (Top Ribbon) and then Edit Interactions.

Hope this will help.

Best Regards,

Gaurav Raj Singh

[rajulshah]

Hello @738o51,

You need to remove Interaction from slicer and the graph via Format > Edit Interactions.

It is because the graph is filtered by the slicer.

Hope this helps.