Forum Discussion
Add JOB ID amount only its current date
Hello, you might have some answers to this. I have a table and I need a sum of the amount of my jobID if the jobID about is its latest date. Below is my table
| ID | Name | Job ID | About | Date |
| 1 | A | 10001 | $ 600.00 | 1/1/2021 |
| 1 | A | 10001 | $ 1,000.00 | 1/1/2022 |
| 1 | A | 10002 | $ 40.00 | 1/1/2021 |
| 1 | A | 10002 | $100,000.00 | 1/1/2022 |
| 1 | A | 30001 | $ 803.00 | 1/1/2021 |
| 1 | A | 30001 | $ 611.00 | 1/1/2022 |
| 1 | A | 401 | $ 214.00 | 1/1/2021 |
| 1 | A | 401 | $ 91,111.00 | 1/1/2022 |
| 1 | A | 12 | $ 85.00 | 1/1/2021 |
| 1 | A | 12 | $ 23.00 | 1/1/2022 |
| 1 | A | 901 | $ 1.00 | 1/1/2021 |
| 1 | A | 901 | $ 700.00 | 1/1/2022 |
| 1 | A | 532 | $ 56.00 | 1/1/2021 |
| 1 | A | 532 | $100,022.00 | 1/1/2022 |
| 1 | A | 222 | $ 34.00 | 1/1/2019 |
| 1 | A | 222 | $ 23.00 | 1/1/2022 |
| 1 | A | 10001 | $ 1.00 | 1/1/2019 |
| 1 | A | 79 | $ 50,000.00 | 1/1/2020 |
so the one with the yellow mark should only be added. So the sum should be
| $343,491.00 |
Hope you can help me. Thanks a lot
Hi, Thanks for the response, appreciate your help by the way can we try this table.
Name JobID Amount Date A 11 $4 1/1/2021 0:00 A 11 $0 1/1/2022 0:00 A 62 $0 1/1/2021 0:00 A 62 $0 1/1/2022 0:00 A 21 $1 1/1/2021 0:00 A 21 $0 1/1/2022 0:00 A 101 $58,493 1/1/2021 0:00 A 101 $53,163 1/1/2022 0:00 A 77 $1,000 1/1/2021 0:00 A 77 $0 1/1/2022 0:00 A 42 $30 9/1/2020 0:00 A 85 $0 1/1/2022 0:00 A 71 $8,507 1/1/2021 0:00 A 71 $10,321 1/1/2022 0:00 A 25 $0 2/1/2021 0:00 A 49 $286 1/1/2021 0:00 A 49 $0 1/1/2022 0:00 A 88 $13,293 2/1/2021 0:00 A 57 $8,326 1/1/2021 0:00 A 57 $8,799 1/1/2022 0:00 so the total should be
$85,606 If you want to total to show correctly as well, try it with a SUMX,
Latest Amount = SUMX ( VALUES ( 'Table'[Job ID] ), VAR _MaxDate = CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Job ID] ) ) RETURN CALCULATE ( SUM ( 'Table'[Amount] ), 'Table'[Date] = _MaxDate ) )Thank you this one works fine!
6 Replies
dreyes6 , Try a measure like
latest About =
VAR __id = MAX ('Table'[Job ID] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[Job ID] = __id )
return
CALCULATE ( Sum ('Table'[About] ), VALUES ('Table'[Job ID] ),'Table'[Job ID] = __id,'Table'[Date] = __date )Thrid last row should not come
Hi, Thanks for the response, appreciate your help by the way can we try this table.
Name JobID Amount Date A 11 $4 1/1/2021 0:00 A 11 $0 1/1/2022 0:00 A 62 $0 1/1/2021 0:00 A 62 $0 1/1/2022 0:00 A 21 $1 1/1/2021 0:00 A 21 $0 1/1/2022 0:00 A 101 $58,493 1/1/2021 0:00 A 101 $53,163 1/1/2022 0:00 A 77 $1,000 1/1/2021 0:00 A 77 $0 1/1/2022 0:00 A 42 $30 9/1/2020 0:00 A 85 $0 1/1/2022 0:00 A 71 $8,507 1/1/2021 0:00 A 71 $10,321 1/1/2022 0:00 A 25 $0 2/1/2021 0:00 A 49 $286 1/1/2021 0:00 A 49 $0 1/1/2022 0:00 A 88 $13,293 2/1/2021 0:00 A 57 $8,326 1/1/2021 0:00 A 57 $8,799 1/1/2022 0:00 so the total should be
$85,606
If you want to total to show correctly as well, try it with a SUMX,
Latest Amount = SUMX ( VALUES ( 'Table'[Job ID] ), VAR _MaxDate = CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Job ID] ) ) RETURN CALCULATE ( SUM ( 'Table'[Amount] ), 'Table'[Date] = _MaxDate ) )Thank you this one works fine!
Commission RenewalDate $238,966 4/1/2020 0:00 $51,718 5/1/2020 0:00 $87,436 6/1/2020 0:00 $204,107 7/1/2020 0:00 $244,060 8/1/2020 0:00 $753,967 9/1/2020 0:00 $47,273 10/1/2020 0:00 $207,433 11/1/2020 0:00 $23,588 12/1/2020 0:00 $280 12/31/2020 0:00 $4,785,567 1/1/2021 0:00 $246,811 3/1/2021 0:00 $224,783 4/1/2021 0:00 $54,839 5/1/2021 0:00 $213,057 6/1/2021 0:00 $64,151 7/1/2021 0:00 $231,259 8/1/2021 0:00 $650,859 9/1/2021 0:00 $45,247 10/1/2021 0:00 $161,187 11/1/2021 0:00 $19,009 12/1/2021 0:00 $0 12/31/2021 0:00 $5,328,343 1/1/2022 0:00 $121,892 3/1/2022 0:00 $180,999 4/1/2022 0:00 $1 5/1/2022 0:00 $0 6/1/2022 0:00
Hello, can you help me with this one? to capture the sum of the year 2022?- Comm =CALCULATE (SUM(table[Commission] ),FILTER (ALL(table[RenewalDate]),YEAR(table[RenewalDate] ) =YEAR ( MAX (table[RenewalDate]))))
i tried this but it won't show the correct total at the bottom. THinking to use SUMX
