Forum Discussion

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
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
segmentation on days difference between two dates.zip75 KB

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

segmentation on days difference between two dates.zip75 KB