I've built a series of measures that calculate specific measures for a group of customers from my data set, called my comp segment.
My measures work, but they are extremely resource intensive and I'm hoping someone on here can take a look and figure a much better way to run these calculations.
So the problem with my comp segment vs most of what I have researched is this: most of the folks I find building comp segments, their criteria is static, and saved in the table already. For example, show me all customers who bought this specific item already, or show me all sales for all items that match this color, etc. Those are all stored in the calculated tables, making a filter criteria fairly simple and not resource intensive.
My segment is dynamic and 12 month trailing for each period.
For ease, the simplest is customer count. So I'll go through that example:
For this period (starts 6/28/2021), show me the 12 month count of customers (ending end of this period) who in the prior 12 months purchased exactly 2 times.
Because it is rolling, it changes every period. I can't store this data in the database (cleanly), so I wrote a virtual table calculation, and this is where it is messy, and I'm hoping there is a much easier way to solve. Before I show this, for reference, the specific table I'm calling is stored so that each Line is one order.
To get the baseline of the customers who purchased twice in the 12 months prior to the last 12 months the first thing I'm doing is summarizing my table, filtering it to the "LLY" range, and creating a new column to count the total number of lines / orders:
VAR pastCustomersSetup =
SUMMARIZE(
CALCULATETABLE(
'CATID Rev Table',
REMOVEFILTERS('CATID Rev Table'[Date]),
REMOVEFILTERS('Period Table'[Start of Period],'Period Table'[Fiscal Year], 'Period Table'[Period #], 'Period Table'[Week Number]),
'CATID Rev Table'[Date] >= lyLYRange &&
'CATID Rev Table'[Date] <= lyRange
),
'CATID Rev Table'[Customer],
"Count of Orders",
CALCULATE(
COUNT('CATID Rev Table'[OrderNbr]),
REMOVEFILTERS('CATID Rev Table'[Date]),
REMOVEFILTERS('Period Table'[Start of Period],'Period Table'[Fiscal Year], 'Period Table'[Period #], 'Period Table'[Week Number]),
'CATID Rev Table'[Date] >= lyLYRange &&
'CATID Rev Table'[Date] <= lyRange
)
)
And doing this in variables, to keep it clean, then I filter that table using a select columns. I need to create a new table, because I am going to use that table to then count the total number of customer (and then sum revenue, and get all my other metrics)
VAR pastCustomers =
SUMMARIZE(
FILTER(
pastCustomersSetup,
[Count of Orders] = 2
),
'CATID Rev Table'[Customer]
)
(ALSO, note. I did change this last one to "SELECTCOLUMNS" instead of summarize, but I got similar, perhaps only slightly better results)
So now I have my table where I show the LLY customers who purchased twice, I need to join that to any customers who purchased in the LY / 12 month prior period, I do this with intersect:
VAR compSegment = INTERSECT(currentCustomers,pastCustomers)
Now I have my compsegment table that I can do what I like with.
For this example, counting my total number of customers, I just do:
COUNTROWS(compsegment)
---
Has anyone had to create a rolling analysis like this before? Like I said, this all works, but takes too long to load. And when I load it to the server, I get the "Not enough memory" error and the visual won't load.
Any help is much appreciated! Let me know if I can provide further examples. The dataset is confidential, but if need be I could create a dummy dataset to show the measures in action.
Thanks all!
