Removing existing date filter before applying new date filter---DAX

[Mkrishna]

Hi, 
I am working on a project. The objective of the project is 

1. To find the number of store visited in last 3 months. For example. If the user select March -2024, then it will be unique store visited in March -2024, Feb -2024 and Jan -2024. Similary, if the user select Feb -2024 then I need to find the store visited in Feb-2024, Jan-2024 and Dec -2023

2. Find the total store that has Brand BBB (or any other brand or product) in the latest visit. I want to have Brand slicer, product slicer. The user will select the value from this slicer and dax will output out of total store visited in last 3 months how many has those brand.

Data Model,

In the data model,

Product is connect to TRAX SOS by product key
Canvas is connected to TRAX SOS by Store key

Date is connectd to Canvas by Date[Date]
Date is connect to TRAX SOS by Date[Date] . this is inactive relationship.

My logic

When useer chooe date in the slicer (say March 2024), the it filter the canvas to show only the store present in March 2024. This Canvas also filter the TRAX SOS to show only March 2024. But I have removed the fitler from Canvas to TRAX SOS. With this I find teh total store visited in last 3 months as follows 

Common Stores =

VAR StartDate = STARTOFMONTH(DATEADD('Date'[Date], -2, MONTH))

VAR FinalDate = MAX('Date'[Date])

VAR Customers = CALCULATETABLE(VALUES('Canvas'[Store Number]))

VAR PriorCustomers = CALCULATETABLE(

    VALUES('TRAX SOS'[store_number]),

    FILTER(

        ALL('Date'),

        'Date'[Date] >= StartDate &&

        'Date'[Date] <= FinalDate

    ), ALL('Product'), ALL(Canvas), USERELATIONSHIP('TRAX SOS'[Visit Date], 'Date'[Date]))

VAR CountCommonCustomers = COUNTROWS(INTERSECT(Customers, PriorCustomers))

VAR Result =

    if(ISBLANK(CountCommonCustomers), 0, CountCommonCustomers)

RETURN

Result

Common Store is correct as per my need.

Now I find, how many store within this common store is visited has brand BB. I want the Brand to be in slicer and not encoded in code.

No of Store 2 =

VAR ReferenceDate = MAX('Date'[Date])

VAR PreviousDate =

    DATESINPERIOD('Date'[Date], ReferenceDate, -3, MONTH)

VAR MaxDate =

    MAXX (

        FILTER (

            ALL ( 'TRAX SOS' ),

            'TRAX SOS'[store_number] = MAX ( 'Canvas'[Store Number] ) &&

            'TRAX SOS'[Visit Date] IN PreviousDate

        ),

        'TRAX SOS'[Visit Date]

    )

VAR STORE =

    CALCULATE (

        COUNTROWS (

            FILTER ( all(

                'TRAX SOS'),

                'TRAX SOS'[store_number] = MAX ( 'Canvas'[Store Number] ) &&

                'TRAX SOS'[Visit Date] = MaxDate &&

                'TRAX SOS'[Visit Date] >= DATE(2023,12,01) &&

                'TRAX SOS'[Visit Date] <= DATE(2024,02,29)

            )

        ),

        USERELATIONSHIP('TRAX SOS'[Visit Date], 'Date'[Date])

    )

RETURN

    IF ( STORE > 0, 1, 0 )

There is problem in second code call Nos of Store 2 especiall in VAR Store calculation. I have used ALL (TRAX SOS ) on the VAR Store calculation. Using ALL gives me the desired result but then it make other slicer coming from Proudct non efffective i.e the result is not affect by slicer. If I do not use ALL in VAR STore calculation, I get only result for 1 month (i.e. for the month selected. I have hardcoded the momnth in the VAR STore calculation for Date rang JAn 2024 to March 2024. I can change this but I am not able to find the replacement for ALL. If I do not use ALL, the date filter coming from Canvas also filter the TRAX SOS and the VAR STORE is calculated for only 1 month. 

REquest for help

[v-yiruan-msft · Accepted solution]

Hi @Mkrishna ,

Please update the formula of variable STORE as below:

VAR STORE =
CALCULATE (
    COUNTROWS (
        FILTER (
            ALL ( 'TRAX SOS' ),
            'TRAX SOS'[store_number] = MAX ( 'Canvas'[Store Number] )
                && 'TRAX SOS'[Visit Date] = MaxDate
                && 'TRAX SOS'[Visit Date] >= DATE ( 2023, 12, 01 )
                && 'TRAX SOS'[Visit Date] <= DATE ( 2024, 02, 29 )
                && 'TRAX SOS'[Brand] IN ALLSELECTED ( 'Product'[Brand] )
        )
    ),
    USERELATIONSHIP ( 'TRAX SOS'[Visit Date], 'Date'[Date] )
)

Best Regards