calculating current month sales for only those dealers on which activity performed in L3M

[Alka735]

Hii All,

Hope you are doing well.

I need help with a requirement: we want to calculate current-month (or month-wise) sales only for those customers who had activity at their counter in the last 3 completed months.

For example, if April is the current month, only customers with activity between January and March should be considered, and their April sales should be included regardless of whether they had activity in April or not.

Please help me with this question.

I am sharing the sample dataset for 2 tables ,Activity and Sales,we need to consider the customers where activity conducted at "Dealer Counter"
and the final output will be 700(total sales)

Date Customer ID Sales Amount

2026-04-02	C001	100
2026-04-10	C002	200
2026-04-12	C003	150
2026-04-15	C004	300
2026-04-18	C005	250
2026-04-20	C006	400

Date Customer ID Activity Conducted At

2026-01-10	C001	dealer Counter
2026-02-15	C002	dealer Counter
2026-03-05	C001	dealer Counter
2026-03-20	C003	dealer Counter
2026-01-25	C004	other
2026-02-10	C005	dealer Counter
2026-04-05	C006	dealer Counter

Customer ID April Sales

C001	100
C002	200
C003	150
C005	250

[Alka735]

Thanks @krishnakanth240 ,it's working fine.

[krishnakanth240]

You are welcome @Alka735 

[pcoley]

@Alka735 Please try with this script:

• Make sure the relationship between Activity and Sales tables is not active (or use CROSSFILTER if needed), because we want independent filtering.
• Replace 'Date' with your actual Date table name if using version

Dealer Counter Current Month Sales = 

VAR CurrentDate = MAX ( 'Date'[Date] )   // Assuming you have a proper Date table
VAR CurrentMonthStart = STARTOFMONTH ( CurrentDate )
VAR Last3MonthsStart  = EDATE ( CurrentMonthStart, -3 )

VAR QualifyingCustomers = 
    CALCULATETABLE (
        VALUES ( Activity[ID] ),
        Activity[Activity Conducted At] = "dealer Counter",
        Activity[Date] >= Last3MonthsStart,
        Activity[Date] < CurrentMonthStart
    )

RETURN
    CALCULATE (
        SUM ( Sales[Sales Amount] ),
        Sales[ID] IN QualifyingCustomers,
        Sales[Date Customer] >= CurrentMonthStart,
        Sales[Date Customer] < EOMONTH(CurrentMonthStart, 0) + 1
    )

[Murtaza_Ghafoor]

@Alka735 

You need to write a DAX to caluclate this 

Sales_Last3M_ActiveCustomers :=

VAR _currentDate = MAX(Sales[Date])
VAR _fromDate = EOMONTH(_currentDate, -4) + 1
VAR _toDate   = EOMONTH(_currentDate, -1)
VAR _customers =
    CALCULATETABLE (

        VALUES ( Activity[Customer ID] ),

        Activity[Activity Conducted At] = "dealer Counter",

        Activity[Date] >= _fromDate,

        Activity[Date] <= _toDate
    )

RETURN
CALCULATE (

    SUM ( Sales[Sales Amount] ),

    Sales[Customer ID] IN _customers

)

[Alka735]

I am trying with this logic, but not getting correct results,some customers sales are skipped while calculation

L3M Active Dealers Current Month Sales = VAR L3M_Dealer_List = CALCULATETABLE( VALUES(DATA1[Dealer ERP Code]), -- Last 3 completed months DATESINPERIOD( 'Calendar'[Date], EOMONTH(MAX('Calendar'[Date]), -1), -3, MONTH ), -- Activity condition DATA1[Activity Conducted At] = "dealer Counter", -- Valid dealer NOT(ISBLANK(DATA1[Dealer ERP Code])) ) RETURN CALCULATE( [TOTAL_SALE], -- Apply only those dealers TREATAS(L3M_Dealer_List, DATA1[Dealer ERP Code]) )

[krishnakanth240 · Accepted solution]

Hi @Alka735 

Can you try this

L3M Active Dealers Current Month Sales =

VAR LastVisibleDate = MAX('Calendar'[Date])

VAR Prev3Months = DATESINPERIOD(

'Calendar'[Date],EOMONTH(LastVisibleDate, -1),-3,MONTH)

VAR DealerList = CALCULATETABLE(

DISTINCT('Activity'[Dealer ERP Code]),Prev3Months,

'Activity'[Activity Conducted At] = "dealer Counter")

RETURN

CALCULATE([TOTAL_SALE],TREATAS(DealerList, 'Sales'[Dealer ERP Code]))