DAX Not Working/ Date is not chaning

Hi Mkrishna ,

It seems like you're facing a challenge with DAX date filtering when trying to calculate the number of stores with Brand BB for the maximum visit date within a specific period.

Let's address the problem step by step:

1. Filtering Store List to Selected Month: You've mentioned that when selecting a month other than December, the Store List correctly filters to that month. This is expected behavior as per your current DAX formula.

2. Filtering Fact Table to a 3-Month Period: The issue arises when you want the Fact Table to filter to a three-month period ending with the selected month. Your current code snippet seems to filter the Fact Table only for the selected month, not the entire period.

To resolve this, we need to adjust your DAX measure to ensure that it considers the entire three-month period. Here's a revised version of your 'Nos. of Store' measure:

Nos. of Store =
VAR SelectedEndDate = MAX('Date'[Date])
VAR StartDate = STARTOFMONTH(DATEADD(SelectedEndDate, -2, MONTH))
VAR EndDate = EOMONTH(SelectedEndDate, 0)
VAR MonthlyStore =
    CALCULATETABLE(
        VALUES('Store List'[Store Number]),
        TREATAS(VALUES('Date'[Date]), 'Store List'[Date]),
        'Store List'[Date] = MIN('Date'[Date])
    )
VAR MaxDate =
    CALCULATE(
        MAX('Fact Table'[Visit Date]),
        FILTER(
            ALL('Fact Table'),
            'Fact Table'[Store Number] IN MonthlyStore &&
            'Fact Table'[Visit Date] >= StartDate &&
            'Fact Table'[Visit Date] <= EndDate
        )
    )
VAR STORE_COUNT =
    CALCULATE(
        COUNTROWS(
            FILTER(
                'Fact Table',
                'Fact Table'[Store Number] IN MonthlyStore &&
                'Fact Table'[Visit Date] = MaxDate &&
                'Fact Table'[Brand] = "BB"
            )
        ),
        REMOVEFILTERS('Date'),
        KEEPFILTERS(VALUES('Date'[Date]))
    )
RETURN
    IF(STORE_COUNT > 0, 1, 0)

-Please note the following changes:

- Defined 'StartDate' and 'EndDate' to represent the start and end of the three-month period.
- Adjusted the 'FILTER' function within 'MaxDate' to consider the entire period between 'StartDate' and 'EndDate'.
- Added a condition to check for Brand BB in the 'STORE_COUNT' calculation.

Please try this revised measure and see if it produces the expected results.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.