topic Re: Active customers count in DAX Commands and Tips

Active customers count

SachinNamdeo-20 — Fri, 06 Jan 2023 05:35:50 GMT

"Dear community,
I have 5 years of data at the invoice level. I am trying to calculate the no. of customers who purchased at least 3 times in the current FY and sales quantity of that customers. FY is an indian fiscal year".
I have this type of data table :-

This is my pbi file link if you have any issue with this then you to this link for visiting data:-

Re: Active customers count

johnt75 — Thu, 05 Jan 2023 11:24:39 GMT

If you have a date table linked to the fact table you can try

Active Customers = VAR SummaryTable = ADDCOLUMNS ( SUMMARIZE ( Sales, Sales[Customer] ), "@num months", COUNTROWS ( CALCULATETABLE ( SUMMARIZE ( Sales, 'Date'[Year month] ) ) ) ) RETURN COUNTROWS ( FILTER ( SummaryTable, [@num months] >= 3 ) )

Re: Active customers count

SachinNamdeo-20 — Thu, 05 Jan 2023 11:46:33 GMT

"Thank you for your valuable suggestion but it gives me blank value, Please suggest me any other measure i also mention my pbi file above"

Re: Active customers count

Anonymous — Thu, 05 Jan 2023 12:12:20 GMT

Active Customers =

COUNTROWS(

FILTER(

SUMMARIZE ( 'Customer Sales', 'Customer Sales'[Dealer], 'Customer Sales'[FY], "@count" , Countrows())

, [@count] >= 3)

)

If you have Invoicecount more than 1 in any line

Active Customers =

COUNTROWS(

FILTER(

SUMMARIZE ( 'Customer Sales', 'Customer Sales'[Dealer], 'Customer Sales'[FY], "@count" , SUM(InvoiceCount))

, [@count] >= 3)

)

Re: Active customers count

johnt75 — Thu, 05 Jan 2023 12:08:36 GMT

Your date table isn't linked to the invoice table. You need to link it on the appropriate field.

Re: Active customers count

SachinNamdeo-20 — Thu, 05 Jan 2023 12:16:14 GMT

"Sorry for mistake sir in this file date is linked with cube invoice but it also return blank value"
https://drive.google.com/file/d/1QPMBDmnmupMDBqSofXjaLCB6OpV3TAKo/view?usp=sharing

Re: Active customers count

FreemanZ — Thu, 05 Jan 2023 12:36:11 GMT

hi SachinNamdeo-20

Not sure if i fully get you, you may try to plot a table visual with two measures like this:

3PlusCustomer = VAR _table = ADDCOLUMNS( VALUES(TableName[Dealer]), "OrderCount", CALCULATE(COUNTROWS(TableName)), "SalesQty", CALCULATE(SUM(TableName[sales])) ) RETURN COUNTROWS(FILTER(_table, [OrderCount]>=3)) 3PlusCustomerSales = VAR _table = ADDCOLUMNS( VALUES(TableName[Dealer]), "OrderCount", CALCULATE(COUNTROWS(TableName)), "SalesQty", CALCULATE(SUM(TableName[sales])) ) RETURN CALCULATE(SUM(TableName[sales]), FILTER(_table, [OrderCount]>=3))

unable to access your file over the cloud and tried to verify with a simplified sample data:

Re: Active customers count

tamerj1 — Thu, 05 Jan 2023 12:46:27 GMT

Hi SachinNamdeo-20
Please refer to amended sample file with the solution

Active Customers = COUNTROWS ( FILTER ( GROUPBY ( SUMMARIZE ( CUBE_INVOICE, CUBE_INVOICE[SOLD TO PARTNER.PARTNER CODE], Calendar_new[YM] ), CUBE_INVOICE[SOLD TO PARTNER.PARTNER CODE], "@Months", SUMX ( CURRENTGROUP ( ), 1 ) ), [@Months] >= 3 ) )

Re: Active customers count

SachinNamdeo-20 — Fri, 06 Jan 2023 05:20:22 GMT

Sir IF we want the same calculation on ficial year how would we can, please suggest

DATESYTD(Calendar_new[Date],"03/31")

Re: Active customers count

SachinNamdeo-20 — Fri, 06 Jan 2023 05:22:42 GMT

"Sir IF we want the same calculation of your given measure on ficial year how would we can, please suggest".

DATESYTD(Calendar_new[Date],"03/31")

Re: Active customers count

SachinNamdeo-20 — Fri, 06 Jan 2023 05:32:32 GMT

calculation done
Active dealer in ficial year = CALCULATE([Active Customers],DATESYTD(Calendar_new[Date],"03/31"))

Re: Active customers count

SachinNamdeo-20 — Fri, 06 Jan 2023 05:34:05 GMT

Thank you sir for your valuable suggestion

Re: Active customers count

SachinNamdeo-20 — Fri, 06 Jan 2023 05:34:33 GMT

Thank you sir for your valuable suggestion

Re: Active customers count

SachinNamdeo-20 — Fri, 06 Jan 2023 05:34:45 GMT

Thank you sir for your valuable suggestion