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...
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

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