In Power BI , there are the following columns in the "BI" table:
Column B: Whether "OTP" is Validated or not (1 = Validated and 0= Not validated)
Column C: OTP number (there are several OTPs per customer)
Column D: Customer name (There are several instances of the same customer name)
Column E: BU location
Column F: Consultant's name
Column G: Month of issue (Example: 1 = January, 2 = February, etc.)
Column H: The current month (Example: 12 = December)
Next, I'd like to create another table with every customer who meets the following conditions:
If all the OTPs of a customer older than 3 months are in state 1, then the customer is "validated".
If a customer's OTPs are in states 2 and 3 over 3 months, but there are no OTPs in state 4 over 3 months, then the customer is "overdue".
If all a customer's OTPs are in state 4, then the customer is "unused".
Thanks for your help.
I tried to create several tables via DAX but never succeeded.