'How to make dynamic RFM scores that adapt according to a chosen period?
Here, I'm trying to set up an RFM on Power BI. But the problem is that I want the calculation of the scores to be different depending on the period. For example, to go from an annual score to a monthly score, I divided the amount by 12.
I tried different ways but nothing works.
Codes I have tried:
First :
M=
var ddate = MAX('DimDate'[Date])
var annuel = DATESINPERIOD('DimDate'[Date],ddate,-1,YEAR)
var semestriel = DATESINPERIOD('DimDate'[Date],ddate,-6,MONTH)
var trimestriel = DATESINPERIOD('DimDate'[Date],ddate,-4,MONTH)
var mensuel = DATESINPERIOD('DimDate'[Date],ddate,-1,MONTH)
var montant = [M]
return
IF(
COUNTROWS(annuel)<= 365,
SWITCH (
TRUE (),
montant <= 10, "1",
montant <= 20, "2",
montant <= 30, "3",
montant <= 40,"4",
montant > 40,"5"
),
IF(
COUNTROWS(semestriel) <= 180,
SWITCH (
TRUE (),
montant <= 10, "1",
montant <= 20, "2",
montant <= 30, "3",
montant <= 40,"4",
montant > 40,"5"
),
IF(
COUNTROWS(trimestriel) <= 120,
SWITCH (
TRUE (),
montant <= 10, "1",
montant <= 20, "2",
montant <= 30, "3",
montant <= 40,"4",
montant > 40,"5"
),
IF(
COUNTROWS(mensuel)<= 30,
SWITCH (
TRUE (),
montant <= 10, "1",
montant <= 20, "2",
montant <= 30, "3",
montant <= 40,"4",
montant > 40,"5"
)
Second
I created a table with the different periods (annual, half-yearly, quarterly) then:
M=
var montant = [M]
var selection = SELECTEDVALUE('Période de segmentation'[Période])
return
IF(
selection = "Annuel" ,
SWITCH (
TRUE (),
montant <= 10, "1",
montant <= 20, "2",
montant <= 30, "3",
montant < 40,"4",
montant >= 40 ,"5"
),
IF(
selection = "Semestriel" ,
SWITCH (
TRUE (),
montant <= 10, "1",
montant <= 20, "2",
montant <= 30, "3",
montant < 40,"4",
montant >= 40 ,"5"
),
IF(
selection = "Trimestriel" ,
SWITCH (
TRUE (),
montant <= 10, "1",
montant <= 20, "2",
montant <= 30, "3",
montant < 40,"4",
montant >= 40 ,"5"
),
IF(
selection = "Mensuel",
SWITCH (
TRUE (),
montant <= 10, "1",
montant <= 20, "2",
montant <= 30, "3",
montant < 40,"4",
montant >= 40 ,"5"
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
