Context Transitioning not working /Code not working

Hi Mkrishna ,

It seems like you are on the right track with your DAX measures, but we need to adjust the logic to ensure that the sales amount reflects all common customers from the intersected store list, regardless of their maximum visit date within the last three months.

For the third part of your goal, where you want to calculate the sales amount, the issue seems to be with the context transition and the way filters are being applied. Here's a revised version of the `Sales Amount` measure that should help:

Sales Amount =
VAR _Reference_Date = MAX('Date'[Date])
VAR _Previous_Dates = DATESINPERIOD(
    'Previous Date'[Date],
    _Reference_Date,
    -3,
    MONTH
)
VAR Customers = VALUES('Store List'[Store Number])
VAR PriorCustomers = CALCULATETABLE(
    VALUES('Fact Table'[Store Number]),
    FILTER(
        ALL('Date'),
        'Date'[Date] >= _Reference_Date - 90 && // Adjust to include the last 90 days
        'Date'[Date] <= _Reference_Date
    )
)
VAR CommonCustomers = INTERSECT(Customers, PriorCustomers)
VAR Sales = CALCULATE(
    SUM('Fact Table'[Sales]),
    FILTER(
        ALL('Fact Table'),
        'Fact Table'[Store Number] IN CommonCustomers &&
        'Fact Table'[Visit Date] >= _Reference_Date - 90 &&
        'Fact Table'[Visit Date] <= _Reference_Date
    )
)
RETURN Sales

In this revised measure:

• We are using DATESINPERIOD to create a date range for the previous three months.
• We then find the common customers by intersecting the customer lists from the 'Store List' and 'Fact Table'.
• Finally, we calculate the sales for these common customers within the last three months.

Please replace the placeholder 90 with the actual number of days that correspond to three months in your context, as months can have varying numbers of days.

For the Total Sales in 3 months measure, ensure that you are not filtering out the stores with visits in October and November. Here's a simplified version that should work:

Total Sales in 3 months =
VAR _Reference_Date = MAX('Date'[Date])
VAR _Start_Date = EDATE(_Reference_Date, -3)
RETURN
IF(
    ISEMPTY('Fact Table'),
    BLANK(),
    CALCULATE(
        SUM('Fact Table'[Sales]),
        FILTER(
            ALL('Fact Table'),
            'Fact Table'[Visit Date] >= _Start_Date &&
            'Fact Table'[Visit Date] <= _Reference_Date &&
            'Fact Table'[Store Number] IN VALUES('Store List'[Store Number])
        )
    )
)

This measure calculates the total sales for the last three months, including stores with a maximum visit date in October or November, not just December.

Please try these revised measures and let me know if they work for you. If you encounter any further issues, please feel free to as me.

Best Regards,

Stephen Tao

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

Anonymous plz do help