I have 2 Problems related to date Range:
I have measure (CLTV) that calculatetes customer life time value (CLTV) https://blog.hubspot.com/service/how-to-calculate-customer-lifetime-value
There is no problem in calculating the formula, but the challenge (CLTV) depends totally when selecting (Two consecutive years) otherwise it will not calculate correctly, My Question how to deal if the User select (2) Years that are Not consecutive from the Years Slicer By alerting the customer to correct his selection or to do condition in the formula to make the calculation(CLTV) only if the years
I made a complete process to calculate customer customer segmentation through (RFM) https://en.wikipedia.org/wiki/RFM_(market_research)
For calculating the recency of the customer I have 1 measure(cstRecency) and 1 calculated column(Cust Recency) that do the same thing as it showed below:
Cust Recency = DATEDIFF([cstLastTrnDate], DATEVALUE(“2018/11/16”), DAY)–>2018/11/16 (Last transaction date in sales oreder data)
cstLastTrnDate = CALCULATE(MAX(Sales[OrderDate]))
The transaction sales date range is from (1/1/2014) Up To (16/11/2018)
My Problem is in filtering the (recency) parameter range (1 to X) and/or selecting the year from slicer
for example :
-If I select the range from (1 to 365) it should filter the Customers segments whom recent visits in 2014 Only and , If I select (366-730) It should gives the customers whom visits (2014 and 2015) and so on.
How to reflect the (RFM) score dynamically based on the filter of the (recency)?
Note: I attached the (pbix) file, It is in Arabic But all the mentioned fields and formulas I make it in English in the Report Name (RFM).
Items Purchased 1.0.pbix (3.8 MB)