Forum Discussion
Count Distinct customer IDs repeating last rolling 90 days
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.
Super UserAnonymous , 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.