Forum Discussion

madmanmark198's avatar
madmanmark198
Frequent Visitor
4 years ago

Best way to create a recency segment

Hi there

 

I have a table with all of my customer donations listed.  Basically many rows of transactions for each customer.  Columns are:

 

  • Transaction ID
  • Date
  • Amount
  • Customer ID

 

I would like to create a segment around recency of payment but I wasnt sure how best to do this  Whether create a column in the data or new table or just a calculated measure.

 

The segment I want to create is basically called "Actives".  I want to effectivly be able to group Active customers in an easy way.  Below is the criteria for this:

 

Anyone who has made a payment in the LAST 12 months + made at least 1 payment prior to the last 12 months.

 

Please help?

 

Many thanks

 

Mark

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi madmanmark198  - I would use the measure something along the lines of the following rather that than adding calculated column.  Note this is a guess and untested because I don't have your data model.

    Count Customers = 
   COUNTA ( Customer[CustomerID] ) 

Count of Active Customers = 
   VAR _Date = TODAY - 365
   VAR _Customers = 
        SUMMARIZE ( 
           Customer ,
           CustomerID , 
           "Is Active" , CALCULATE ( COUNTA[Transaction[Transaction ID] ),
                             FILTER ( Calendar , Calendar[Date] >=  _Date ) )
        )
   VAR _Filter = FILTER ( _Customers , [Is Active] > 0 )
   VAR _Result = COUNTROWS ( _Filter )
RETURN
   _Result