Calculated column to count number of transactions for each customer in a period of time

[wmeyer]

I'm trying to calculate the number of transactions each customer had in a given 18 month period of time. Here is what I have so far, but it is not calculating the correct number of transactions. The formula below is a calculated column.

Lost Count # Trans2 = CALCULATE(COUNTX('Customer Base','Customer Base'[Transaction ID]),DATESBETWEEN('Customer Base'[Transaction Date],'Customer Base'[Start of Month of Transaction DAte],[Lost Month]))

I have 1 table that contains all transactions for all customers. It includes:Customer ID, transaction date, start of month of transaction date, lost month

For example it looks like this:

Customer ID    Transaction ID     Start of Month of Transaction  Lost Month            Count of Trans(formula above)

ABC                    1                              1/1/15                                      3/1/16              3

ABC                    2                              4/1/15                                      7/1/16              2

DEF                    3                               1/1/15                                      3/1/16             1

ABC                   4                                7/1/15                                      10/1/16           1

The numbers in bold in the Count of Trans column are what I want to see, but currently the formula is giving me a 1 for each, because it is not grouping the customer ID's together and counting them collectively for that period of time. 

The idea is to then be able to say, if a customer has a 1, that means the customer has not ordered in that 18 month time period

[v-juanli-msft]

Hi @wmeyer

Try this formula instead

Column 2 =
CALCULATE (
    COUNTROWS ( 'Customer Base' ),
    FILTER (
        ALLEXCEPT ( 'Customer Base', 'Customer Base'[Customer ID] ),
        'Customer Base'[Start of Month of Transaction]
            >= EARLIER ( 'Customer Base'[Start of Month of Transaction] )
    )
)

Best regards

Maggie

[wmeyer]

Maggie, This appears to have worked, partially. I have some rows where it is not calculating the correct totals.

I'm confused how this works with out using the Lost Month Date column. 

Could you help me understand what each function in the formula is doing? I'd like to get a better understanding so i can use these functions and apply them to other things done the road, once we get this figured out. 

[v-juanli-msft · Accepted solution]

Hi @wmeyer

try this

Column 2 =
IF (
    [ Lost Month ] <> BLANK (),
    CALCULATE (
        COUNTROWS ( 'Table1' ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Customer ID] ),
            (
                Table1[Start of Month of Transaction]
                    >= EARLIER ( Table1[Start of Month of Transaction] )
            )
                && ( Table1[Start of Month of Transaction] ) <= EARLIER ( Table1[ Lost Month ] )
        )
    ),
    CALCULATE (
        COUNTROWS ( 'Table1' ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Customer ID] ),
            Table1[Start of Month of Transaction]
                >= EARLIER ( Table1[Start of Month of Transaction] )
        )
    )
)

explanation:

Column 2 =
IF (
    [ Lost Month ] <> BLANK (),          //judge if the 'Lost Month' column is blank//
    CALCULATE (
        COUNTROWS ( 'Table1' ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Customer ID] ),     //returen a table with all filters removed except for the filter on the 'Customer ID' column//
            (
                Table1[Start of Month of Transaction]
                    >= EARLIER ( Table1[Start of Month of Transaction] )   // filter the context which date is larger than or equal 
to the current row  context //    

                             )
                && ( Table1[Start of Month of Transaction] ) <= EARLIER ( Table1[ Lost Month ] )
        )                                                                                                 //meet two conditions above at the same time//
    ),
    CALCULATE (
        COUNTROWS ( 'Table1' ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Customer ID] ),
            Table1[Start of Month of Transaction]
                >= EARLIER ( Table1[Start of Month of Transaction] )       
        )
    )
)

Best regards

Maggie

[wmeyer]

Maggie, This is now calculating correctly for all rows! Thank you so much! You saved me countless hours trying to figure this out 🙂 

How did you become so good at formulas? I'm looking for a mentor/training or something that can help me become better at them.