Forum Discussion
Ranking
| 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.
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)
6 Replies
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)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.)
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.
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.
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
