Best way to create a recency segment

[madmanmark198]

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

[Daryl-Lynch-Bzy]

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