Add JOB ID amount only its current date

[dreyes6]

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

[dreyes6 · Accepted solution]

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

[jdbuchanan71 · Accepted solution]

@dreyes6 

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 )
)

[jdbuchanan71 · Accepted solution]

@dreyes6 

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 )
)

[dreyes6]

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?

[dreyes6]

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

[dreyes6 · Accepted solution]

Thank you this one works fine!

[amitchandak]

@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

[dreyes6 · Accepted solution]

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