Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!