Count clients with sales in distinct months

[mayara_alencar]

I'm trying to create  a measure where I'm counting the distinct clients from a vendor where the client has bought something in the selected month and also in the other two months before. Example: the select month is august and the client has to have sales in august and also in july or june. What i tried to use but is not working:

Active Clients=

var temp = SUMMARIZECOLUMNS( vw_sales[StartOfMonth],vw_sales[codclient], vw_sales[codvendor],

"actualsales",[.Sales R$],

"salesothermonths", [.Compra meses anteriores])

RETURN

CALCULATE(distinctcount(vw_sales[codclient]),  FILTER(temp, not ISBLANK(sumx(temp, [salesothermonths])) &&sumx(temp, [actualsales])>0))

I also tried and didn't work:

Var salesothermonths =  CALCULATE(

    distinctcount(vw_sales[codclient]),

    UNION(

        DATEADD(dimTempo[data], -1, MONTH),

        DATEADD(dimTempo[data], -2, MONTH)

    )

)

var actualsales = CALCULATE(distinctcount(vw_sales[codclient]), DATEADD(dimTempo[data],0,MONTH))

var ttclientes = distinctcount(vw_sales[codclient])

RETURN

SWITCH(TRUE(),actualsales>0 && salesothermonths>0,ttclientes)

Help?

[divyed]

Hello @mayara_alencar ,

You can mofiy your dax to achieve the goal :

Active Clients =
VAR SelectedMonth = SELECTEDVALUE(vw_sales[StartOfMonth]) -- The selected month
VAR DateRange =
DATESINPERIOD(
vw_sales[StartOfMonth],
SelectedMonth,
-2,
MONTH
) -- Create a 3-month range including the selected month and 2 prior months
RETURN
CALCULATE(
DISTINCTCOUNT(vw_sales[codclient]), -- Count distinct clients
FILTER(
vw_sales,
vw_sales[StartOfMonth] IN DateRange && -- Check if the StartOfMonth falls within the range
vw_sales[Sales R$] > 0 -- Ensure there are sales
)
)

Modify variable and column names as per need.

I hope this helps.

Did I answer your query ? Mark this as solution if this helps, Kudos are appreciated.

Cheers

[Jihwan_Kim]

Hi,

I tried to create a sample pbix file like below, and please check the below picture and the attached pbix file whether it suits.

Client count: = 
VAR _t = SUMMARIZE ( sales, client[client] ) 
RETURN 
COUNTROWS ( _t )

WINDOW function (DAX) - DAX | Microsoft Learn

Client count also in the otehr two months before: =
VAR _t =
    WINDOW (
        -2,
        REL,
        0,
        REL,
        ALL ( 'calendar'[Year-Month], 'calendar'[Year-Month sort] ),
        ORDERBY ( 'calendar'[Year-Month sort], ASC )
    )
RETURN
    CALCULATE ( [Client count:], _t )