cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
akfir
Helper V
Helper V

Customer status in a specific date

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

akfir_0-1684737275920.png

2. Orders:

akfir_1-1684737325538.png

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

akfir_2-1684737489332.png

Thanks in advance

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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]
)

View solution in original post

1 REPLY 1
tamerj1
Super User
Super User

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]
)

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors