Forum Discussion

rafarother's avatar
rafarother
Frequent Visitor
5 years ago

Repurchase counter

Hello guys, 
this is my first post and I need help. 

I have a huge database with the columns: day, state, costumer code, SKU, origin, state and month. 
I need to count how many times a costumer made a purchase in a month, but I need to mark when this code appeared that month. If it is the first time, mark with 1, the second with a 2, etc.

I can do that on Excel: =COUNTIFS($D$2:D10087;D10087;$E$2:E10087;E10087) where D is the Costumer Code and E is the Month. (I do that every line from D2 to D10087)

I tried that on Power BI, creating a new table:
Calc_Repurchase = 
SUMMARIZE(db, db[Costumer Code], db[Month], "Count", DISTINCTCOUNT(db[Day]))

It works, but it only shows the total number of times the customer made the purchase in a month. And isn't what I want. 

I created a new column at the original table using Column = COUNTX(FILTER(db, db[Day] <= EARLIER(db[Day])), db[Costumer Code]), but returned a message error tha my computer doesn´t have enough memory. 
 
 
Regards, 

Rafael

 

CountIfs
POWER BI Dax rules
repurchase

3 Replies

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    Please provide data with some expected results per instructions below. 

     

    How to get good help fast. Help us help you.
    How to Get Your Question Answered Quickly - Give us a good and concise explanation
    How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    rafarother , Output you want is not clear. Please check last post how to provide the information.

     

    I think you need day rank. Try if this can work

    rankx(filter(db, [Costumer Code] = earlier([Costumer Code]) && [Month] && earlier([Month])), db[Day],,asc,dense)

    • rafarother's avatar
      rafarother
      Frequent Visitor
      5 years ago

      I want something like that:



      But yes, I think i need a rank.




      I can do it that way:
      count_purchase =
      COUNTROWS(FILTER(db, db[Customer Code] = EARLIER(db[Customer Code]) && db[Month] = EARLIER(db[Month]) && db[Day] <= EARLIER(db[Day])))

      This works, but in a small database. In the huge database (21000000 lines), said I don´t have enough memory. (My work notebook have only 4GB RAM.  Sad 😕  )