Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Segmentation

Hello,

 

I looking for a solution for the following problem. I need to be able to analyze data for a period based on the first occurence for the customer in the period and its previous activity. I have categorization rule I have to apply. Per exemple if the difference between its first activity in the period and the previous (which is not in the period) is 0 or 1 days, it is category 1. if it is between 2 and 5 days is is category 2. Else it is category 3.

I have a customer and date dimension, a fact table who track customer activity by date. Something like that

Customer idDateValue
12017-11-301
22017-11-302
12017-11-293
12017-11-284
12017-11-275
12017-11-266
12017-11-227
12017-11-168
12017-11-019

 

 

I use the date from the date dimension like a slicer to select the period I want to analyse.  So according the rule I explained:

if the period is between 2017-11-28 and 2017-11-30, I need  measures telling me there is 1 Cat1 (customer #1), 0 in cat 2 and 1 Cat3 (Customer #2).

if the period is betwwen 2017-11-26 and 2017-11-28., the mesaures Cat 1 = 0, Cat 2 = 1 (Customer #1), Cat 3 = 0,....

 

 

I tried to apply SQLBI, Segmentation pattern, but it categorise me a customer in several category. A customer can be in  only  1 category for the period selected.

 

To compute easily difference, I added a last actity column in the fact table where there is the date of the previous actity for the customer, I don't know if there is a good way to get this date using DAX.

 

I hope I am clear. 

 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    I think I finally found the solution by Adjusting Dynamic Segmentation pattern to my case:

    Cat = 
CALCULATE ( DISTINCTCOUNT(Customer[Id]),
FILTER ( ADDCOLUMNS ( Customer,"CustomerDiff",
CALCULATE ( FIRSTNONBLANK(TOPN ( 1, VALUES (FactTable[DiffInDays] ), FactTable[DiffInDays] ),1),
CALCULATETABLE ( Customer )
,ALLSELECTED ()
)
),
COUNTROWS (
FILTER (
'Cat', NOT(ISBLANK([CustomerDiff])) &&
[CustomerDiff] >= Cat[MinDiff]
&& [CustomerDiff] <= Cat[MaxDiff]
)
) > 0
)

     

    As I am not able to work with the earlier function I adjusted the formula: 

    Previous Date = 
var currentDate = FactTable[Date]
var currentCustomer = FactTable[Id]
VAR val = CALCULATE(MAX('FactTable'[Date]),
FILTER(ALL('FactTable'),
'FactTable'[Date] < currentDate
&& 'FactTable'[Id] = currentCustomer
)
)
return IF(ISBLANK(val),DATEVALUE("2017/01/01"),val)

    Is Earlier function better ?

     

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Anonymous，

    When the period is between 2017-11-28 and 2017-11-30, the previous date for customer 1 is 2017-11-27, right? If so, please create the following columns in your fact table.

    previous date = CALCULATE(FIRSTNONBLANK(Table1[Date],Table1[Date]),FILTER(Table1,Table1[Customer id]=EARLIER(Table1[Customer id])&& Table1[Value]=EARLIER(Table1[Value])+1))

    Daysdiff = DATEDIFF(Table1[previous date],Table1[Date],DAY)

    Then create the following measures in the fact table.

    maxday per customer = MAX(Table1[Daysdiff])

    Category = IF([maxday per customer]<>BLANK()&&([maxday per customer]= 0 || [maxday per customer]= 1), "Cat 1",IF([maxday per customer]<>BLANK() && [maxday per customer]>=2 && [maxday per customer]<=5,"Cat 2","Cat 3" ))




    Regards,
    Lydia

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks Anonymous

       

      I tried your solution but I ahve error with the first formula : previous date = CALCULATE(FIRSTNONBLANK(Table1[Date],Table1[Date]),FILTER(Table1,Table1[Customer id]=EARLIER(Table1[Customer id])&& Table1[Value]=EARLIER(Table1[Value])+1))

       

      EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

       

      Moreover, I am not sure this will work because the "Value" column can have any value so the Table1[Value]=EARLIER(Table1[Value])+1 will not work.

       

      My other question is, will this  solution work for creating a measure for each category. I need to be able to create something like a KPI for each category and having a measure "Category" won't allow me to di that, right ?

       

      Thanks for your help.

       

      Here is the link to my working version: https://1drv.ms/u/s!Ag5lje3-r6cXwRU7dXN9Bam7gLOG

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Anonymous,

        I am not able to access the file you shared. In your scenario, you can add a index column in Query Editor, then right click your table and choose "New column" to apply the following DAX.


        previous date = CALCULATE(FIRSTNONBLANK(Table1[Date],Table1[Date]),FILTER(Table1,Table1[Customer id]=EARLIER(Table1[Customer id])&& Table1[Index]=EARLIER(Table1[Index])+1))

        Besides, what KPI would you like to create? Could you please post expected result in table format?

        Regards,

        Lydia