Forum Discussion
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-msftResident 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
- AnonymousNot 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.
Looking forward to seeing the solution you have come up with!