Forum Discussion
Customers Based on Order History
I need help identifying and categorizing customers in Power BI based on the selected date using the following conditions: If a customer has not placed an order for more than 3 months as of the s...
Here is your solution in a matrix table. Ensure to break the date relationship between calender and data table.
status =
var selected_month = [Last Date in selected Month]
var last_invoice_date = CALCULATE(max(data[invoice_date]),ALLEXCEPT(Customer,Customer[Company_Group]),data[invoice_date]<=selected_month)
var first_invoice_date = CALCULATE(min(data[invoice_date]),ALLEXCEPT(Customer,Customer[Company_Group]),data[invoice_date]<=selected_month)
var differance = DATEDIFF(last_invoice_date,selected_month,MONTH)
var Diff_initial_date = DATEDIFF(first_invoice_date,selected_month,MONTH)
return
switch(TRUE(),differance>3,"Lost Customer", differance<=3&&Diff_initial_date>=12,"Existing Customer",differance<=3&&Diff_initial_date<12,"New Customer","NA")
Hi Rupak_bi - I cannot break the date relationship between the calendar and data table, as my other tables rely on this relationship as well.