topic Re: Count clients with sales in distinct months in DAX Commands and Tips

Count clients with sales in distinct months

mayara_alencar — Fri, 10 Jan 2025 19:52:34 GMT

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?

Re: Count clients with sales in distinct months

Jihwan_Kim — Sat, 11 Jan 2025 03:34:37 GMT

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 )

Re: Count clients with sales in distinct months

divyed — Sat, 11 Jan 2025 09:06:50 GMT

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

Re: Count clients with sales in distinct months

mayara_alencar — Mon, 13 Jan 2025 13:45:12 GMT

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.

Re: Count clients with sales in distinct months

mayara_alencar — Mon, 13 Jan 2025 14:01:49 GMT

This also didn't work 😢

Re: Count clients with sales in distinct months

mayara_alencar — Mon, 13 Jan 2025 14:09:05 GMT

This is an example of what i mean:

And this should be the result:

Here's the sample:

Sample table

Re: Count clients with sales in distinct months

Anonymous — Tue, 21 Jan 2025 07:00:42 GMT

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