Identify Months of activity per customer

[mattrixdesign2]

Trying to think how best to go about a measure my boss has asked for.

He wants data on "active" accounts, meaning by customer how many months have the traded.

E.g. Customer ABC Ltd, traded in Jan, Feb, March - so 3 months of trade

Customer 123 PLC, traded Jan, March, April, Nov, Dec - so 5 months of trade

For this to be relative, I think it will need to be based on the prior 12 months rolling rather than a calendar year.

I could quite easily visualize this, but the aim is more to identify customers by "activity" (i.e. months traded), then put them in buckets 0-3 Low, 4-6 Medium, 7-12 High (based on last 12 moths)

My data is on an invoice table like this (each row is the invoice date)

Customer	Part	Invoice Date
ABC LTD	XXXXXX	01/01/2022
ABC LTD	YYYYYY	20/02/2022
ABC LTD	LLLLLL	03/03/2022
123 PLC	XXXXXX	21/01/2022
123 PLC	XXXXXX	03/03/2022
123 PLC	YYYYYY	14/04/2022
123 PLC	YYYYYY	10/11/2022
123 PLC	YYYYYY	25/12/2022

[mattrixdesign2]

Thanks for this. 

It functions, but it doesnt quite do what I wanted it to (my fault for not being clear).

I dont want the count of activity on each month, rather I want to know if a month has had any activity at all (i.e. an active month, if its 1 or 1000 transactiosn), then the how many months have been active.

Example, results I want to see is for Jan there was activity, sames for Feb, so 1 for Jan, 1 for Feb, means 2 months of activity (in the last 12 months). 

[v-rzhou-msft]

Hi @mattrixdesign2 ,

I suggest you to create a Calendar table to help Calculation.

Calendar = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2022, 01, 01 ), DATE ( 2023, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMMM" ),
    "MonthSort", MONTH ( [Date] )
)

Measure:

Buckets = 
VAR _RANGEEND =
    MAX ( 'Calendar'[Date] )
VAR _RANGESTART =
    EOMONTH ( _RANGEEND, -12 ) + 1
VAR _Filter12Period =
    FILTER (
        'Table',
        'Table'[Invoice Date] >= _RANGESTART
            && 'Table'[Invoice Date] <= _RANGEEND
    )
VAR _Count =
    COUNTAX ( _Filter12Period, [Invoice Date] )
RETURN
    SWITCH (
        TRUE (),
        _Count <= 3, "0-3 Low",
        _Count <= 6, "4-6 Medium",
        _Count <= 12, "7-12 High"
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.