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?

[v-yaningy-msft]

Hi, @mayara_alencar 

Thanks for the reply from divyed and Jihwan_Kim. You can try the following dax to achieve your need.

Active = 
VAR _slicerMonth = SELECTEDVALUE(Slicer[month])
VAR _earlierMonth = _slicerMonth - 2
VAR _month = SELECTEDVALUE('Table 1'[month])
VAR _result = IF(_month>=_earlierMonth && _month <=_slicerMonth,CALCULATE(COUNTROWS('Table 2'),FILTER('Table 2','Table 2'[Active?] = "Active")))
RETURN
_result

Inactive = 
VAR _slicerMonth = SELECTEDVALUE(Slicer[month])
VAR _earlierMonth = _slicerMonth - 2
VAR _month = SELECTEDVALUE('Table 1'[month])
VAR _result = IF(_month>=_earlierMonth && _month <=_slicerMonth,CALCULATE(COUNTROWS('Table 2'),FILTER('Table 2','Table 2'[Active?] = "Inactive")))
RETURN
_result

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[mayara_alencar]

This is an example of what i mean: 

And this should be the result:

Here's the sample:

Sample table

[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

[mayara_alencar]

This one didn't work. It only counted the clients that have sales>0 in the current month. It should count the ones that also have sales in the 2 previous months.

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

[mayara_alencar]

This also didn't work 😢