Forum Discussion
Solved
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 ...
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!
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