topic Re: calculating current month sales for only those dealers on which activity performed in L3M in DAX Commands and Tips

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

Alka735 — Mon, 04 May 2026 12:23:33 GMT

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

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

Alka735 — Mon, 04 May 2026 12:25:45 GMT

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]) )

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

Murtaza_Ghafoor — Mon, 04 May 2026 20:19:25 GMT

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

)

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

pcoley — Tue, 05 May 2026 00:33:19 GMT

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 )

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

krishnakanth240 — Tue, 05 May 2026 04:32:44 GMT

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]))

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

Alka735 — Tue, 05 May 2026 10:13:30 GMT

Thanks krishnakanth240 ,it's working fine.

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

krishnakanth240 — Tue, 05 May 2026 12:29:49 GMT

You are welcome Alka735