Forum Discussion
Dax
Hi Everyone, I have two tables Sales & Calendar. Sales contain columns 'customerName', 'salesDate', and 'productAmount'. While the calendar contains all the dates. I have created a new column...
Anonymous , Please find the example in the file . In case you need to count or filter please follow dynamic segmentation in the video
File after signature
New active = var _count = countrows(Sales)+ 0
var _diff =Datediff(CALCULATE(Max(Sales[Sales Date]), Filter(ALL('Date'), 'Date'[Date] <= Max('Date'[Date]))), Max('Date'[Date]), day)
return
Switch( True(),
Max('Date'[Date]) = Max(Sales[Sales Date]) , "Active",
Max('Date'[Date]) < Minx(ALLEXCEPT(Sales,Customer[Name]), Sales[Sales Date]), blank(),
Max('Date'[Date]) = Minx(ALLEXCEPT(Sales,Customer[Name]), Sales[Sales Date]), "New",
_diff<=30 , "Inctive",
_diff >60 , "Lost", "Partially Lost")
Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q