Forum Discussion
Count Distinct customer IDs repeating last rolling 90 days
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).
Hi Anonymous
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 countValueKindly 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.
3 Replies
- amitchandak
Super User
Anonymous , 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
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-jingzhangCommunity Support
Hi Anonymous
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 countValueKindly 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.
