Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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 |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
96 | |
92 | |
87 | |
69 |
User | Count |
---|---|
161 | |
126 | |
119 | |
109 | |
95 |