Date Range Problem Need To be solved

DAX DAX Calculations
1

Hi EveryBody
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).
Items Purchased 1.0.pbix (3.8 MB)

2

If the statement below evaluates to >2, more- or non consecutive years are selected
COUNTROWS( GENERATESERIES( YEAR(__MinDate), YEAR(__MaxDate), 1 ))

You can built IF or SWITCH/TRUE logic around it to inform on an invalid selection.

.

Sorry to say I’m not sure what to make of your second request. Can you provide a clear example with expected outcome. Thanks.

I hope this is helpful.

3

Hi @MAAbdullah47, we’ve noticed that no response has been received from you since the 31st of July. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

4

Thank you @Melissa and sorry for not replaying last days because I was out of town and now get back, for the 1st Q I will try it, for the second question I’ll try to explain again:
We have around 5 Years in the data (2014-2018) whaich mean If I filter from (1-365) it will bring the customers whom visit the stores in 2014 and If I put (366-730) that means will bring 2015, and so on.
But If I put (371-935) it will cover early of 2015 and Middle of 2016, that is what I means.
Thank you for your help again.
If you need more information in understanding (RFM) in particuler for recency you can check this websites

Thank you

5

Hi @Melissa , Please look at the following calculated column in the file:
Cust Recency = DATEDIFF([cstLastTrnDate], DATEVALUE(“2018/11/16”), DAY)
the secret of the solution problem here , I need to replace the date above (DATEVALUE(“2018/11/16”)) by the maximum correspond date in the maximum number (from-to) that I was explained in my previous replay. example: (366-973) we assume the number correspond to 973=2016/10/15.

6

Hi @MAAbdullah47,

Thanks for clearing that up. :+1:
I will get back to you later today.

7

Hi @MAAbdullah47,

First a few quick notes:

  1. I’ve seen that you have 2 bi-directional filters in your model, that you don’t need in my opinion so look into changing that.
  2. I’ve marked your Date table as date table (you can find that Option on the Table tools ribbon).

.

I’ve seen that your Date table also starts 1/1/2014 so I’ve added a Date Index column starting from 1, to your Date table. The logic for your calculated column in the Customer table then becomes:

Cust Recency = 
//DATEDIFF([cstLastTrnDate], DATEVALUE("2018/11/16"), DAY)
LOOKUPVALUE( Dates[Date Index], Dates[Date], [cstLastTrnDate] )

I hope this is helpful.
Items Purchased 1.0.pbix (3.8 MB)

1 Like
8

Thank you so much @Melissa , I’ll check then get back to you.

9

Hi @Melissa

I think when you add the date index in the date every thing is better now so I think for Q2 it is done , what about Q1 still I didn’t understand it how to add the switch/if can you help me?

10

Sure thing apologies I thought that was sorted.
I’ve added an IF statement at the end of your CLTV Measure here:

CLTV = 
    VAR __Customers = COUNTROWS(VALUES(Sales[Customer Name Index]))
    VAR __MaxDate = MAX(Sales[OrderDate])
    VAR __MinDate = MIN(Sales[OrderDate])
    VAR __Years = 
        IF(
            __MaxDate - __MinDate > 365,
            YEAR(__MaxDate) - YEAR(__MinDate) + 1,
            1
        )
    VAR __AveragePurchaseFrequency = 
        DIVIDE(
            DIVIDE(
                COUNTROWS('Sales'), 
                __Years, 
                BLANK()
            ),
            __Years,
            BLANK()
        )
    /// Average Order Size Or Average Sales
  ///  VAR __AveragePurchaseValue = [Average Sales] 
    VAR __AveragePurchaseValue = [Average Order Size]
    //AVERAGE('R06_Table'[Value])
    VAR __AverageCustomerValue = __AveragePurchaseValue * __AveragePurchaseFrequency
    VAR __AverageCustomerLifespan =  DIVIDE(1,[Avg Customer Lifespan],0)
    //1/[Avg Customer Lifespan]
RETURN

IF( COUNTROWS( GENERATESERIES( YEAR(__MinDate), YEAR(__MaxDate), 1 ))>2,
    "Select 2 consecutive years",
    DIVIDE(__AverageCustomerValue * __AverageCustomerLifespan, __Customers, BLANK())
)
1 Like
11

Thank you So Much @Melissa