Active customers count

[SachinNamdeo-20]

"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:-
 

[johnt75 · Accepted solution]

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

[tamerj1]

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

[SachinNamdeo-20]

Thank you sir for your valuable suggestion 

[FreemanZ]

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:

[SachinNamdeo-20]

Thank you sir for your valuable suggestion 

[Anonymous]

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)

    )

[johnt75 · Accepted solution]

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

[SachinNamdeo-20]

Thank you sir for your valuable suggestion 

[SachinNamdeo-20]

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

[SachinNamdeo-20 · Accepted solution]

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

[SachinNamdeo-20]

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

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

[SachinNamdeo-20]

"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"

[johnt75]

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

[SachinNamdeo-20]

"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