Forum Discussion

ExcelMonke's avatar
ExcelMonke
Icon for Impactful Individual rankImpactful Individual
2 years ago

Rolling 3 month not filtering correctly

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)

 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    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.