topic Re: Date Range calculation problems if the user select years Not consecutive in DAX Commands and Tips

Date Range calculation problems if the user select years Not consecutive

MAAbdullah_47 — Thu, 30 Jul 2020 07:02:55 GMT

I have 2 Problems related to date Range:
Problem-1
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
are consecutive?

Problem-2
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).https://drive.google.com/file/d/1251v--r1h8B2Uglnv8jlxd1erDqHvhNm/view?usp=sharing

Re: Date Range calculation problems if the user select years Not consecutive

AllisonKennedy — Thu, 30 Jul 2020 08:39:14 GMT

For problem 1 can you just have them select start year and then do calculation based on the selected year and previous year?

What result do you want the measure to give if more than 2 consecutive years are selected?

Re: Date Range calculation problems if the user select years Not consecutive

MAAbdullah_47 — Thu, 30 Jul 2020 09:44:44 GMT

AllisonKennedy Sorry I ddnt understand your point can you explain,
If I Understand Your Question The CLTV start calculating from after the first year, in our example after the year 2014, so if the user select 2014 the result will be 0 , After that the User should select single or more than one year but should be consecutive (successive) e.g. 2015,2017,2018 , If the user select 2015,2017 it will calculate but it will not reflect the real correct case.
You can look at the measure (CLTV) in the pbix file.

Re: Date Range calculation problems if the user select years Not consecutive

MAAbdullah_47 — Wed, 05 Aug 2020 07:35:18 GMT

Still the problem didn't solved.

topic Re: Date Range calculation problems if the user select years Not consecutive in DAX Commands and Tips

Date Range calculation problems if the user select years Not consecutive

Problem-2I made a complete process to calculate customer customer segmentation through (RFM) https://en.wikipedia.org/wiki/RFM_(market_research)

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).https://drive.google.com/file/d/1251v--r1h8B2Uglnv8jlxd1erDqHvhNm/view?usp=sharing

Re: Date Range calculation problems if the user select years Not consecutive

Re: Date Range calculation problems if the user select years Not consecutive

Re: Date Range calculation problems if the user select years Not consecutive

Problem-2
I made a complete process to calculate customer customer segmentation through (RFM) https://en.wikipedia.org/wiki/RFM_(market_research)