Ignore Page Level Filter on a Power BI Calculation Group

[JimmyKhan2022]

I have a Financial Year filter that does not select before 2021 . This is because there is PAGE level Filter that only allows the last 2 years , so only 2021 to 2023 .

Problem is that I have a 14 w Rolling Average, that when the 1st week in 2021 is selected, it does not work .

Code for measure 

    CALCULATE (
            DISTINCTCOUNT ( Sales[activity_id] ), 
            FILTER (
                Sales,
                Sales[done] = "Yes"
                    && Sales[activity_type]
                    IN { "In-Person Meeting", "Virtual Meeting", "Site Visit" }
            ) )


    Code for Caluclation group 
            VAR __lastVisibleDate =
            LASTDATE ( 'Calendar'[Date] )
        VAR _fromDate =
            CALCULATE ( DATEADD ( __lastVisibleDate, -91, DAY ), ALL ( 'Time') )
        VAR __result =
           CALCULATE (
               SELECTEDMEASURE (),
             REMOVEFILTERS ( 'Calendar' ), 
          
               'Calendar'[Date] > _fromDate
                    && 'Calendar'[Date] <= __lastVisibleDate
            ) 
            
        VAR _result_divide =
            DIVIDE ( __result, 13, 0 )
        RETURN
        
        
            IF ( _result_divide < 0.1, BLANK (), _result_divide )

I have tried every combination of ALL('Calendar' ) to get the rolling 14 week average to work for the 1st week of Jan 2021 . Debugging the _fromDate does give me the 20/10/2020 but the rolling average still does not work out correctly ! Argghhh !

Any ideas ? This is proving quite diffcicult

The PAGE level filter is needed to allow the user to select the financial year .

[amitchandak · Accepted solution]

@JimmyKhan2022 , If you do not want more week then selected then you can get avg with help date/calendar table

14 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value)))
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-14,MONTH))

Or window function

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc