Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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 |
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 )
)
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 )
)
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?
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 |
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
58 | |
57 |