Forum Discussion

SachinNamdeo-20's avatar
SachinNamdeo-20
Helper II
3 years ago
Solved

Active customers count

"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's avatar
    johnt75
    3 years ago

    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's avatar
    SachinNamdeo-20
    3 years ago
    calculation done
    Active dealer in ficial year =     CALCULATE([Active Customers],DATESYTD(Calendar_new[Date],"03/31"))

13 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    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's avatar
      SachinNamdeo-20
      Helper II
      3 years ago

      "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's avatar
        johnt75
        Super User
        3 years ago

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

    • SachinNamdeo-20's avatar
      SachinNamdeo-20
      Helper II
      3 years ago

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

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

       

    • SachinNamdeo-20's avatar
      SachinNamdeo-20
      Helper II
      3 years ago

      "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's avatar
        SachinNamdeo-20
        Helper II
        3 years ago
        calculation done
        Active dealer in ficial year =         CALCULATE([Active Customers],DATESYTD(Calendar_new[Date],"03/31"))
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago
    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)
        )
     
  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    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:

     

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

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