Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Identify & Count Customer Status per Month

Hello,

 

I am trying to create a column that will identify my customers with 1 of 4 possible status's: Active, New, Returning or Inactive. Ultimately, I would like it to be able to calculate the sum of each status type for any given month/year/quarter, so I suppose it would be just as well to have this as a measure that sums the number of each type for any given month/year quarter.


Active: Created a Form in a month

New: Created a Form in a month and have never created a Form before that month (first form)

Returning: Created a Form in a month, and did not create a Form in the previous month

Inactive: Did not create a Form in a month or the previous month

 

Below is a sample of my table. This table shows the customer ID (OrganizationID), and the dates they have created forms (DateCreated). There is a row for every form they have ever created. A customer may have multiple rows per day. I already have a column that calculates the date their first Form was created. I also have a Dates table that is joined to the DateCreated date in the documents table.

 

How would I write a dax statement (Column or Measure) that would show me the number of Active, New, Returning, and Inactive customers for a given month/quarter/year?

 

 

Table Sample Data:

 

Here is what I am hoping to acheive in either the form of a column or measure:

 

 

Any help, guidance, ideas or suggestions would be appreciated! I have been noodling this for a while and cannot seem to come up with a solution.

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar

    Hi Anonymous,

     

    If it is conveninet, please share your data sample which could reproduce your scenario as table format so that I can copy and test.

     

    Best  Regards,

    Cherry

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi Cherry, 

       

       

      You bet. Here is a Dropbox link to the sample data I posted. It is an excel file. If you require a different file type, please let me know.

       

      Sample Data File

       

      Looking forward to seeing the solution you have come up with!