Ranking

[Anonymous]

Part	Date	time	Before	After	DESIRED RESULT
a	1/1/2022	9:00:00 AM		0.1	0
a	1/1/2022	9:15:00 AM	0.1	0.7	0
a	1/1/2022	11:00:00 AM	0.7	0.3	0
a	1/1/2022	3:00:00 PM	0.3	0.2	1
b	1/5/2022	8:00:00 AM		1	0
b	1/5/2022	11:30:00 AM	1	5	0
b	1/5/2022	4:00:00 PM	5	3	0
b	1/5/2022	11:00:00 PM	3	2	1

I have this file. and I would like to know how to acheive the desired resluts column.  I have multiple parts that get changes made to the item file multiple times per day.  I would like to know which change was made last according to Date and Time combination.  Each part will need to have either a 0 or a 1 designator.

[Swilson2112]

EXAMPLE of tables and desired result.

TABLE 1                                       

Transaction #.      Part#                           Invoice Date.

1.                             A                              02/14/2022

2.                             A                              02/02/2022

TABLE 2

Part#                            Chnage Date.           Change

A.                                   02/10/2022.                1.25

A                                    02/05/2022                 0.37

A                                    01/15/2022                 2.56

Desired Resultes

Transaction   Return Result.     Notes:

1                      1.25                Chnage date of 02/10/2022 is closest less than date from invoice date of 02/14/2022 

2                      2.56                Chnage date of 01/15/2022 is closest less than date from invoice date of 02/02/2022

[Swilson2112]

I figured this part out.  Now I am trying to create a measure that compares the invoice date from table 1 with the closest lower Change date from table 2 and then returns the changed field from table 2. 

[Swilson2112]

Part	Before	After	Date Time	Desired RandRank
A		0.415	2/8/19 11:09	0
A		0	2/8/19 11:09	0
A		0	2/8/19 11:09	0
A		24.1	2/8/19 11:09	0
A		0.065	2/8/19 11:09	0
A		103.5	2/8/19 11:09	0
A		103.5	2/8/19 11:09	0
A		46.5	2/8/19 11:09	0
A		36.5	2/8/19 11:09	0
A		36.5	2/8/19 11:09	0
A		36.5	2/8/19 11:09	0
A		36.5	2/8/19 11:09	0
A	36.5		2/8/19 11:12	1
A	0.415	0.49	2/13/19 6:27	0
A	103.5	76.5	2/13/19 6:29	0
A	103.5	76.5	2/13/19 6:29	1
A	0.065	0.0865	2/14/19 10:14	1
A	0.49	0.5195	2/27/19 12:19	1
A		46.5	4/18/19 8:41	1
A	0.5195	0.4495	6/14/19 15:17	1
A	76.5	103.5	2/20/20 16:03	0
A	76.5	103.5	2/20/20 16:03	1
A	24.1	38.98	6/5/20 14:20	1
A	0.4495	0.91	5/17/21 10:48	1
A	0.91	1.12	9/22/21 16:30	1
A	1.12	0.4495	12/3/21 7:28	0
A	0.4495	1.12	12/3/21 7:30	1
A	1.12	0.4495	12/10/21 6:53	0
A	0.4495	0.91	12/10/21 7:10	0
A	0.91	1.12	12/10/21 7:12	0
A	1.12	0.91	12/10/21 7:18	0
A	0.91	1.12	12/10/21 7:19	1
A	1.12	0.4395	1/26/22 16:09	0
A	0.4395	0.8455	1/26/22 16:42	0
A	0.8455	0.91	1/26/22 16:43	0
A	0.91	0.965	1/26/22 16:44	0
A	0.965	1.02	1/26/22 16:45	0
A	1.02	1.12	1/26/22 16:46	1

OK, How do I create this desired result?  I have many parts and want the desired rank of either "0" or "1".  On the last combination of Part & DateTime for each specific day.  (See table above with Desired Result column that I manually populated.). I have multiple changes happening each day, but I only want to work with the last one of each day.  I will then need to compare the ship date of another table to the closest after date that contains a "1" and then ruturn the text volue of the After field. (Text field type because it is also other changes) from the above table.  I thought that I would need the 0's and 1's but if there is an easier way to do this I am more than willing to listen.

[Anonymous]

Thanks Greg.  This worked.

[Greg_Deckler · Accepted solution]

@Anonymous Maybe:

DESIRED RESULT column =
  VAR __Part = [Part]
  VAR __Date = [Date]
  VAR __time = [time]
  VAR __MaxDate = MAXX(FILTER('Table',[Part] = __Part),[Date])
  VAR __MaxTime = MAXX(FILTER('Table',[Part] = __Part && [Date] = __MaxDate),[time])
RETURN
  IF([Part] = __Part && [Date] = __Date && [time] = __time,1,0)

[Swilson2112]

Part	Before Cost	After Cost	Date Time	Desired RandRank
A		0.415	2/8/19 11:09	0
A		0	2/8/19 11:09	0
A		0	2/8/19 11:09	0
A		24.1	2/8/19 11:09	0
A		0.065	2/8/19 11:09	0
A		103.5	2/8/19 11:09	0
A		103.5	2/8/19 11:09	0
A		46.5	2/8/19 11:09	0
A		36.5	2/8/19 11:09	0
A		36.5	2/8/19 11:09	0
A		36.5	2/8/19 11:09	0
A		36.5	2/8/19 11:09	0
A	36.5		2/8/19 11:12	1
A	0.415	0.49	2/13/19 6:27	0
A	103.5	76.5	2/13/19 6:29	0
A	103.5	76.5	2/13/19 6:29	1
A	0.065	0.0865	2/14/19 10:14	1
A	0.49	0.5195	2/27/19 12:19	1
A		46.5	4/18/19 8:41	1
A	0.5195	0.4495	6/14/19 15:17	1
A	76.5	103.5	2/20/20 16:03	0
A	76.5	103.5	2/20/20 16:03	1
A	24.1	38.98	6/5/20 14:20	1
A	0.4495	0.91	5/17/21 10:48	1
A	0.91	1.12	9/22/21 16:30	1
A	1.12	0.4495	12/3/21 7:28	0
A	0.4495	1.12	12/3/21 7:30	1
A	1.12	0.4495	12/10/21 6:53	0
A	0.4495	0.91	12/10/21 7:10	0
A	0.91	1.12	12/10/21 7:12	0
A	1.12	0.91	12/10/21 7:18	0
A	0.91	1.12	12/10/21 7:19	1
A	1.12	0.4395	1/26/22 16:09	0
A	0.4395	0.8455	1/26/22 16:42	0
A	0.8455	0.91	1/26/22 16:43	0
A	0.91	0.965	1/26/22 16:44	0
A	0.965	1.02	1/26/22 16:45	0
A	1.02	1.12	1/26/22 16:46	1

OK, How do I create this desired result?  I have many parts and want the desired rank of "1" on the last combination of Part & DateTime for each specific day.  (See table above with Desired Result column that I manually populated.)