Forum Discussion

akfir's avatar
akfir
Helper V
3 years ago
Solved

Customer status in a specific date

I have to data tables: 
1. Status changes of customers:

2. Orders:

I wish to add a column to Orders table which presents the customer status as of each customer at its order date, as below:

Thanks in advance

crosscheck
Customers
status
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi akfir 

    please try

    Status While Ordering =
    MAXX (
    TOPN (
    1,
    FILTER (
    ALL ( StatusChanges ),
    StatusChanges[Customer Code] = Orders[Customer Code]
    && StatusChanges[Date] <= Orders[Date]
    ),
    StatusChanges[Date]
    ),
    StatusChanges[New Status]
    )

1 Reply

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi akfir 

    please try

    Status While Ordering =
    MAXX (
    TOPN (
    1,
    FILTER (
    ALL ( StatusChanges ),
    StatusChanges[Customer Code] = Orders[Customer Code]
    && StatusChanges[Date] <= Orders[Date]
    ),
    StatusChanges[Date]
    ),
    StatusChanges[New Status]
    )