Context Transitioning not working /Code not working

[Mkrishna]

Hi All,

I am new to PBI and this is my data model

My goal is to caculate as follows 
1. If user select date December 2023 then the code should first find the common store in Fact table and Store list visited in last 3 months from date selected i.e. the should look for date range of December , Nov and Oct in Fact Table and find common store by doing interecept with Store List. the code that i wrote is 

Common Customers =

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

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

VAR Customers = VALUES('Store List'[Store Number])

VAR PriorCustomers = CALCULATETABLE(

    VALUES('Fact Table'[Store Number]),

    FILTER(

        ALL('Date'),

        'Date'[Date] >= StartDate &&

        'Date'[Date] <= FinalDate

    ), ALL('Product'))

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

RETURN

    CountCommonCustomers

This code give the total customer that have been visited in last 3 month from the selected date. This code works fine.


2. Now I want to find how many store as found in above code has product BB. (I used Filter in pane for fitlering BB)

Nos. of Store =

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 MaxDate = MAXX(

    FILTER(

        ALL('Fact Table'),

        'Fact Table'[Store Number] = MAX('Store List'[Store Number]) &&

        'Fact Table'[Visit Date] IN _Previous_Dates

    ),

    'Fact Table'[Visit Date]

)

VAR STORE =

CALCULATE(

    COUNTROWS(

        FILTER(

            'Fact Table',

            'Fact Table'[Store Number] = MAX('Store List'[Store Number]) &&

            'Fact Table'[Visit Date] = MaxDate

        )

    ),

    REMOVEFILTERS('Date'),

    KEEPFILTERS(_Previous_Dates),

    USERELATIONSHIP('Date'[Date], 'Previous Date'[Date])

)

RETURN IF(STORE > 0, 1, 0)

This gives the the store then I have used summarize in next measure. This also work fine and it give me my desired answer.

3. Now I am interested in finding the Sales amount. I have a column called Sales in Fact Table and I used this code 

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] >= _Previous_Dates &&

        'Date'[Date] <= _Reference_Date

    )

)

VAR CommonCustomers = INTERSECT(Customers, PriorCustomers)

VAR MaxDate = MAXX(

    FILTER(

        ALL('Fact Table'),

        'Fact Table'[Store Number] = MAX('Store List'[Store Number]) &&

        'Fact Table'[Visit Date] IN _Previous_Dates

    ),

    'Fact Table'[Visit Date]

)

VAR Sales =

CALCULATE(

    SUMX(

        FILTER(

            'Fact Table',

            'Fact Table'[Store Number] = MAX('Store List'[Store Number]) &&

            'Fact Table'[Visit Date] = MaxDate

        ),

        'Fact Table'[Sales]

    ),

    ALL('Date'),  -- Remove filters on the 'Date' column

    KEEPFILTERS(_Previous_Dates),  -- Preserve filter context on _Previous_Dates

    USERELATIONSHIP('Date'[Date], 'Previous Date'[Date])

)

RETURN Sales

The part of the code is as follows

Total Sales in 3 months = 

var x = DIVIDE(CALCULATE(SUMX(SUMMARIZE('Store List', 'Store List'[Store Number]), [Sales Amount])),1000)

return

IF(ISEMPTY('Fact Table'), BLANK(), x)

I have used summarize in next me as for code (2) which is shown above. I find there is problem in  code (3) for calcualtion of VAR Sales.  The reason is because when I make a table and put all the store name in first column, common customer is second column and Total Sales in 3 month, I only get for the selected month i.e if the selected month is Dec , Total Sales in 3 month is shown only for the store whose max visit is Dec but is shows blank for other store whose max visit date is either Oct or Nov. 
 
Please do help

[v-stephen-msft]

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.           

[Mkrishna]

Thank you @v-stephen-msft for the reply. I report is montlhy one. I cannot do -90 days as some month will have 31 and some 30 and Feb can have 29/28 days.

[v-stephen-msft · Accepted solution]

Hi @Mkrishna ,

Then you could try EDATE() function. The function returns the date that is the indicated number of months before or after the start date. 

Modified:

Sales Amount =
VAR _Reference_Date =
    MAX ( 'Date'[Date] )
VAR Customers =
    VALUES ( 'Store List'[Store Number] )
VAR PriorCustomers =
    CALCULATETABLE (
        VALUES ( 'Fact Table'[Store Number] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Date] >= EDATE ( _Reference_Date, 3 )
                && '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] >= EDATE ( _Reference_Date, 3 )
                    && 'Fact Table'[Visit Date] <= _Reference_Date
        )
    )
RETURN
    Sales

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.           

[Mkrishna]

@v-cgao-msft plz do help