I am trying to create a dynamic RFM analysis based on the Year and Month selected from a slicer. The logic is: if Year = 2025 and Month = April are selected, then go back two years from the selected period, retrieve all the transactions within that range, and perform the RFM calculation on that data. I was able to achieve this using a measure, and the following table works as expected.
But now I want to create a dashboard that shows the total number of customers and their sales by each Sub_Category. However, I'm not able to get the correct result and need help.
I have already created a measure that calculates everything perfectly, but for some reason, when I use the measure in a visual, it doesn’t return the correct results.
[Measure] Customer Count by RFM SubCategory =
VAR EndDate = [Selected Date]
VAR StartDate = [Last2Years Start Date] -- 24 months back
VAR selected_coverage_model = [Selected Coverage Model]
VAR selected_coverage_model_default = [Selected Coverage Model (Default Value)]
-- Step 1: Filter invoices in the rolling 24-month window
VAR CustomerTable =
ADDCOLUMNS(
SUMMARIZE(
FILTER(
'Invoice Header',
'Invoice Header'[invoice_date] >= StartDate &&
'Invoice Header'[invoice_date] <= EndDate &&
(
ISBLANK(selected_coverage_model) ||
'Invoice Header'[salesforce_coverage_model_combined] = selected_coverage_model
)
),
'Invoice Header'[salesforce_id_customer_id_combined]
),
"Recency",
CALCULATE(
DATEDIFF(
CALCULATE(
MAX('Invoice Header'[invoice_date]),
FILTER(ALL('Calendar'),'Calendar'[Date] >= StartDate && 'Calendar'[Date] <= EndDate)
),
EndDate,
DAY
)
),
"Frequency",
CALCULATE(
DISTINCTCOUNT('Invoice Header'[invoice_number]),
FILTER(ALL('Calendar'),'Calendar'[Date] >= StartDate && 'Calendar'[Date] <= EndDate)
),
"Monetary",
CALCULATE(
SUM('Invoice Detail'[net_line_amount_cad]),
FILTER(ALL('Calendar'),'Calendar'[Date] >= StartDate && 'Calendar'[Date] <= EndDate)
)
)
-- Step 2: Bucket R, F, M into 1-5 scale (example using percentiles)
VAR RFM_Scored =
ADDCOLUMNS(
CustomerTable,
"R_Score",
MAXX
(
FILTER(
'RFM Criteria',
[Recency] >= 'RFM Criteria'[R Criteria (Min)] &&
[Recency] <= 'RFM Criteria'[R Criteria (Max)] &&
'RFM Criteria'[Coverage Model] = selected_coverage_model_default
),
'RFM Criteria'[Score]
),
"F_Score",
MAXX
(
FILTER(
'RFM Criteria',
[Frequency] >= 'RFM Criteria'[F Criteria (Min)] &&
[Frequency] < 'RFM Criteria'[F Criteria (Max)] &&
'RFM Criteria'[Coverage Model] = selected_coverage_model_default
),
'RFM Criteria'[Score]
),
"M_Score",
MAXX
(
FILTER(
'RFM Criteria',
[Monetary] >= 'RFM Criteria'[M Criteria (Min)] &&
[Monetary] < 'RFM Criteria'[M Criteria (Max)] &&
'RFM Criteria'[Coverage Model] = selected_coverage_model_default
),
'RFM Criteria'[Score]
)
)
-- Step 3: Concatenate into 3-digit RFM score
VAR RFM_Categorized =
ADDCOLUMNS(
RFM_Scored,
"RFM_Score",
[R_Score] * 100 + [F_Score] * 10 + [M_Score]
)
-- Step 4: Join with mapping table to get Sub_Category
VAR WithSubCategory =
ADDCOLUMNS(
RFM_Categorized,
"Sub_Category",
LOOKUPVALUE(
'RFM Categories'[Sub_Category],
'RFM Categories'[RFM Score],
[RFM_Score]
)
)
VAR WithCategory =
ADDCOLUMNS(
WithSubCategory,
"Category",
LOOKUPVALUE(
'Categories'[Category],
'Categories'[Sub_Category],
[Sub_Category]
)
)
-- Step 5: Return count of customers in each Sub_Category (driven by visual context)
RETURN
CALCULATE(
COUNTROWS(
FILTER(
WithCategory,
[Sub_Category] IN VALUES('Categories'[Sub_Category])
)
),KEEPFILTERS('Categories')
)
