Return a latest status in fact table

[abatahir]

I have two tables, table1 is the fact table, tabble2 is the Status table.
Now, Table1 has a unique product ID and table2 multiple as the status and date. 
I have created the relation one to many and Cross Filter Direction BOTH, and created a Measure on Revenue Column in table1 with date filter from calender table which in relation with both tables.
Drag the Revenue measure in Matrix measure and Status from table two. the total to the matrix is correct but the breakup in from of each status is wrong. 
if download the data from visual from and do total in excel its total difference number. 

[parry2k]

@abatahir not sure what you are looking for, let me know if this is what you are expecting, some of these id's doesn't have a record in table2 and I used "**No Status Found"

[littlemojopuppy]

@parry2k thank you for helping!  🙂

[abatahir]

I am sorry I did not find option for attachment. 
Table1

ID	value	Sale Date
1444	200	12-12-21
1445	1239	01-07-21
1446	60	25-01-21
1447	60	05-07-21
1448	60	14-02-21
1449	60	27-01-22
1450	60	22-03-21
1451	60	13-01-22
1452	60	24-06-21
1453	60	14-09-21
1454	354	18-07-21
1455	101	07-02-22
1456	354	10-11-21
1457	101	14-07-21
1458	525	14-10-21
1459	1035	06-03-21
1460	380	21-10-21
1461	380	13-07-21
1462	770	29-08-21
1463	3801	06-05-21
1464	407	18-11-21
1465	2116	20-03-21
1466	915	30-01-22

table2

Latest Status	ID	Status Date
Canceled	1444	14-12-21
Canceled	1445	03-07-21
Delivered	1446	27-01-21
Ready To Ship	1446	27-01-21
Shipped	1446	27-01-21
Delivered	1447	07-07-21
Ready To Ship	1447	07-07-21
Shipped	1447	07-07-21
Delivered	1448	16-02-21
Ready To Ship	1448	16-02-21
Shipped	1448	16-02-21
Delivered	1449	29-01-22
Ready To Ship	1449	29-01-22
Shipped	1449	29-01-22
Delivered	1450	24-03-21
Ready To Ship	1450	24-03-21
Shipped	1450	24-03-21
Delivered	1451	15-01-22
Ready To Ship	1451	15-01-22
Shipped	1451	15-01-22
Delivered	1452	26-06-21
Ready To Ship	1452	26-06-21
Shipped	1452	26-06-21
Delivered	1453	16-09-21
Ready To Ship	1453	16-09-21
Shipped	1453	16-09-21

[littlemojopuppy]

Hi @abatahir 

Question: how do you resolve things like this?  Transaction 1447 has three different statuses on the same day. 

What determines "latest"?  I assume statuses go through a progression: ready to ship, shipped, delivered.

[abatahir]

This sample date does not show the time with date, but actually, its have time with date from the time we can filter. 

[littlemojopuppy]

Hi @abatahir I'm stumped, mostly because I'm attempting this on three hours sleep.  😔  Calling in help from people who have forgotten more about Power BI than I ever knew.

@parry2k @mahoneypat Can you guys please try to help him out?  I'd hate to leave him hanging.  This is the approach I was taking:

1. Determine the most recent status update for each transaction
2. Determine the history ID number for each of those updates
3. Apply those history ID numbers as a filter when calculating revenue

But...getting same amount for each status.

The filter context is being screwed up (most likely by using ALL) but my brain is fried and haven't been able to get it right.  Hopefully I'm not too far off and you guys can help him.

[littlemojopuppy]

Hi @abatahir can you provide some sample data to play with?