Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Different Rolling Average Timing show the same result

 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!

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI Anonymous ,

    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

3 Replies

  • rajulshah's avatar
    rajulshah
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    Hello Anonymous,

     

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Anonymous ,

    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