Forum Discussion

paweldm's avatar
paweldm
Helper II
9 years ago
Solved

segmentation on days difference between two dates

Hi,   Problem: how can I perform segmentation of customers based on the DiffDates (number of days between today and date of the last transaction) with use of ranges defined in a separate and non-co...
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi paweldm,

     

    In your scenario, you can create a new table from the Sales table:

     

    Table = SUMMARIZE('Table1','Table1'[CustomerID],"LastTransaction",MAX('Table1'[date]))

     

    Then create calculated columns in this table:

    DiffDates = if(ISBLANK([LastTransaction]),BLANK(),VALUE(TODAY()-[LastTransaction]))

     

    Range = SWITCH(TRUE(),'Table'[DiffDates]>0 && 'Table'[DiffDates]<30,"Hot",'Table'[DiffDates]>30 && 'Table'[DiffDates]<60,"Warm",'Table'[DiffDates]>60 && 'Table'[DiffDates]<180,"Cold",'Table'[DiffDates]>180 && 'Table'[DiffDates]<9999999,"Frozen")

     

    Create a relationship between the Sales table and this new table use CustomerID. Drag Range column from the new table into a slicer visual. For more information, please check attached .pbix.

     

     

    Best Regards,
    Qiuyun Yu

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

Hi paweldm,

 

In your scenario, you can create a new table from the Sales table:

 

Table = SUMMARIZE('Table1','Table1'[CustomerID],"LastTransaction",MAX('Table1'[date]))

 

Then create calculated columns in this table:

DiffDates = if(ISBLANK([LastTransaction]),BLANK(),VALUE(TODAY()-[LastTransaction]))

 

Range = SWITCH(TRUE(),'Table'[DiffDates]>0 && 'Table'[DiffDates]<30,"Hot",'Table'[DiffDates]>30 && 'Table'[DiffDates]<60,"Warm",'Table'[DiffDates]>60 && 'Table'[DiffDates]<180,"Cold",'Table'[DiffDates]>180 && 'Table'[DiffDates]<9999999,"Frozen")

 

Create a relationship between the Sales table and this new table use CustomerID. Drag Range column from the new table into a slicer visual. For more information, please check attached .pbix.

 

 

Best Regards,
Qiuyun Yu

  • paweldm's avatar
    paweldm
    Helper II
    9 years ago
    Thank you ! It is brilliante solution.
  • paweldm's avatar
    paweldm
    Helper II
    9 years ago
    v-qiuyu-msft wrote:

    Hi paweldm,

     

    In your scenario, you can create a new table from the Sales table:

     

    Table = SUMMARIZE('Table1','Table1'[CustomerID],"LastTransaction",MAX('Table1'[date]))

     

    Then create calculated columns in this table:

    DiffDates = if(ISBLANK([LastTransaction]),BLANK(),VALUE(TODAY()-[LastTransaction]))

     

    Range = SWITCH(TRUE(),'Table'[DiffDates]>0 && 'Table'[DiffDates]<30,"Hot",'Table'[DiffDates]>30 && 'Table'[DiffDates]<60,"Warm",'Table'[DiffDates]>60 && 'Table'[DiffDates]<180,"Cold",'Table'[DiffDates]>180 && 'Table'[DiffDates]<9999999,"Frozen")

     

    Create a relationship between the Sales table and this new table use CustomerID. Drag Range column from the new table into a slicer visual. For more information, please check attached .pbix.

     

     

    Best Regards,
    Qiuyun Yu

    Is there a solution to add to the report one more measure to calculate frequency of invoices?

     

    The frequency is to be calculated as a Customer LifeTime (calculated in days since the first transaction till today)  divided by TotalInvoices (calculated as a total number of invoices generated by a customer during its LifeTime)

     

    My current tabel report has the following columns and works exelent with "temperature" slicer:

    CustomerID | LastTransaction | DiffDates