Count Distinct customer IDs repeating last rolling 90 days

[Bobiverse]

I have a table with customer IDs, week-end dates, sales.

I am able to create a table that shows by month ending date the distinct count of customer IDs in the last rolling 90 days.

However unable to figure out, how to get a count of distinct customer IDs that repeat more then x1 in the last rolling 90 days.

With other words, I need to see by month for the last rolling 90 days the # of distinct customers buying (which I figured out) & also the count of disctinct customers buying more then one time in the same rolling period (which I am unable to figure out).

[amitchandak]

@Bobiverse , You can create a measure like

new measure =
var _x =2
return
countx(filter(summarize(table, Table[customer] , "_1", count(table[customer])), [_1] >_x),[customer])

to make x dynamic you can use what if parameter - https://docs.microsoft.com/en-us/power-bi/desktop-what-if

[Bobiverse]

@amitchandak , thank you for the recomendation. I tried it but the numbers do not match the manual calculated expected outcomes for the measure.

See below table that approximataly shows what the desired KPI outcome should populate ...

[v-jingzhang · Accepted solution]

Hi @Bobiverse 

I am not sure what your data looks like so I use some simple data to realize this. Please download this PBIX file for details.

I create a table to get the transaction count of each customer in a month and will use it in a measure.

Monthly_Count = 
SUMMARIZE (
    'Table',
    'Table'[Last WE of Month],
    'Table'[Customer ID],
    "Count In A Month", COUNT ( 'Table'[Customer ID] )
)

Then create a measure to get the number of customers buying more than one time in 90 days.

Measure_2 = 
VAR thisWeekEOM =
    MAX ( 'Table'[Last WE of Month] )
VAR _table =
    FILTER (
        'Monthly_Count',
        'Monthly_Count'[Last WE of Month] <= thisWeekEOM
            && 'Monthly_Count'[Last WE of Month] > thisWeekEOM - 90
    )
VAR _table2 =
    SUMMARIZE (
        _table,
        'Monthly_Count'[Customer ID],
        "Count in 90 Days", SUM ( 'Monthly_Count'[Count In A Month] )
    )
VAR countValue =
    COUNTX (
        FILTER ( _table2, [Count in 90 Days] > 1 ),
        'Monthly_Count'[Customer ID]
    ) + 0
RETURN
    countValue

Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.