Rolling 3 month not filtering correctly

[ExcelMonke]

Hello, I am currently struggling with getting a rolling 3 month calculation to work as expected. In the screenshot below you can see that the column "Avg. Open Days per Week", counts the day name where a location is open (in this case it is all Monday, and 1 is the correct return). I added in a rolling 3 months column that calculates the previous 3 months depending on a date slicer (in this case the max date is 12/31/2023). Can someone help me understand why dates past 9/1/2023 are being calculated? 

For context, each row header is a "WeekBeginning" data point from a Date Dim Table. 

Here are the various DAX measures I have tried that continuoulsy gave me an incorrect result:

Measure try #1:

VAR _Dates = LASTDATE('Date Dimension'[Date])

VAR _DaysWeek = 
CALCULATE (
    COUNT ( 'Date Dimension'[DayName] ),
    FILTER (
        'FactTable',
        'FactTable'[Include] = "Include" 
    ),   
    ALLEXCEPT ( 'Date Dimension', 'Date Dimension'[WeekBeginningDT] )
)

VAR _Rolling3 = CALCULATE(_DaysWeek,DATESINPERIOD('Date Dimension'[Date],MAX('Date Dimension'[Date]),-3,MONTH),
    FILTER(ALL('Date Dimension'),'Date Dimension'[Date]<= _Dates)) 

RETURN
_Rolling3

Measure try #2:

Rolling3MonthDaysOpen = 
VAR _Dates = LASTDATE('Date Dimension'[Date])

VAR _DaysWeek = 
CALCULATE (
    COUNT ( 'Date Dimension'[DayName] ),
    FILTER (
        'FactTable',
        'FactTable'[Include] = "Include" 
       && 'FactTable'[Date] <=_Dates &&
        // 'FactTable'[Date] >DATEADD(_Dates,-3,MONTH)
    ),
    DATESINPERIOD('Date Dimension'[Date],MAX('Date Dimension'[Date]),-3,MONTH),
    ALLEXCEPT ( 'Date Dimension', 'Date Dimension'[WeekBeginningDT] )
)


RETURN
_DaysWeek

Measure Try #3:

Rolling3MonthDaysOpen = 
VAR _Dates = LASTDATE('Date Dimension'[Date])

VAR _DaysWeek = 
CALCULATE (
    COUNT ( 'Date Dimension'[DayName] ),
    FILTER (
        'FactTable',
        'FactTable'[Include] = "Include" 
    ),
FILTER(ALL('Date Dimension'),'Date Dimension'[Date]<= _Dates 
&& 'Date Dimension'[Date]>DATEADD(_Dates,-3,MONTH)
    
    ALLEXCEPT ( 'Date Dimension', 'Date Dimension'[WeekBeginningDT] )
)


RETURN
_DaysWeek

For reference, the "Avg. Open Days per Week" measure is as follows (and works as intended):

DaysOpenPerWeek = 
VAR _DaysWeek = 
CALCULATE (
    COUNT ( 'Date Dimension'[DayName] ),
    FILTER (
        'FactTable',
        'FactTable'[Include] = "Include" 
    ),
    ALLEXCEPT ( 'Date Dimension', 'Date Dimension'[WeekBeginningDT] )
)

VAR _WeeksPerMonth = DISTINCTCOUNT('Date Dimension'[WeekBeginningDT])

RETURN
ROUND((_DaysWeek/_WeeksPerMonth),0)

[v-zhengdxu-msft]

Hi @ExcelMonke 

This may be caused by the using of the time intelligence function, please consider to use some explicit measures to get the right target.

If this can't help please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples.

It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.