Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
dreyes6
Helper I
Helper I

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

IDNameJob ID About Date
1A10001 $      600.001/1/2021
1A10001 $    1,000.001/1/2022
1A10002 $        40.001/1/2021
1A10002 $100,000.001/1/2022
1A30001 $      803.001/1/2021
1A30001 $      611.001/1/2022
1A401 $      214.001/1/2021
1A401 $  91,111.001/1/2022
1A12 $        85.001/1/2021
1A12 $        23.001/1/2022
1A901 $          1.001/1/2021
1A901 $      700.001/1/2022
1A532 $        56.001/1/2021
1A532 $100,022.001/1/2022
1A222 $        34.001/1/2019
1A222 $        23.001/1/2022
1A10001 $          1.001/1/2019
1A79 $  50,000.001/1/2020

 

 

 

dreyes6_0-1652405511147.png

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

3 ACCEPTED SOLUTIONS

Hi, Thanks for the response, appreciate your help by the way can we try this table.

NameJobIDAmountDate
A11$41/1/2021 0:00
A11$01/1/2022 0:00
A62$01/1/2021 0:00
A62$01/1/2022 0:00
A21$11/1/2021 0:00
A21$01/1/2022 0:00
A101$58,4931/1/2021 0:00
A101$53,1631/1/2022 0:00
A77$1,0001/1/2021 0:00
A77$01/1/2022 0:00
A42$309/1/2020 0:00
A85$01/1/2022 0:00
A71$8,5071/1/2021 0:00
A71$10,3211/1/2022 0:00
A25$02/1/2021 0:00
A49$2861/1/2021 0:00
A49$01/1/2022 0:00
A88$13,2932/1/2021 0:00
A57$8,3261/1/2021 0:00
A57$8,7991/1/2022 0:00

 

so the total should be 

$85,606

 

 

dreyes6_0-1652407779495.png

 

View solution in original post

jdbuchanan71
Super User
Super User

@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_0-1652408402674.png

 

View solution in original post

Thank you this one works fine!

View solution in original post

6 REPLIES 6
jdbuchanan71
Super User
Super User

@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_0-1652408402674.png

 

CommissionRenewalDate
$238,9664/1/2020 0:00
$51,7185/1/2020 0:00
$87,4366/1/2020 0:00
$204,1077/1/2020 0:00
$244,0608/1/2020 0:00
$753,9679/1/2020 0:00
$47,27310/1/2020 0:00
$207,43311/1/2020 0:00
$23,58812/1/2020 0:00
$28012/31/2020 0:00
$4,785,5671/1/2021 0:00
$246,8113/1/2021 0:00
$224,7834/1/2021 0:00
$54,8395/1/2021 0:00
$213,0576/1/2021 0:00
$64,1517/1/2021 0:00
$231,2598/1/2021 0:00
$650,8599/1/2021 0:00
$45,24710/1/2021 0:00
$161,18711/1/2021 0:00
$19,00912/1/2021 0:00
$012/31/2021 0:00
$5,328,3431/1/2022 0:00
$121,8923/1/2022 0:00
$180,9994/1/2022 0:00
$15/1/2022 0:00
$06/1/2022 0:00


Hello, can you help me with this one? to capture the sum of the year 2022?


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

Thank you this one works fine!

amitchandak
Super User
Super User

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hi, Thanks for the response, appreciate your help by the way can we try this table.

NameJobIDAmountDate
A11$41/1/2021 0:00
A11$01/1/2022 0:00
A62$01/1/2021 0:00
A62$01/1/2022 0:00
A21$11/1/2021 0:00
A21$01/1/2022 0:00
A101$58,4931/1/2021 0:00
A101$53,1631/1/2022 0:00
A77$1,0001/1/2021 0:00
A77$01/1/2022 0:00
A42$309/1/2020 0:00
A85$01/1/2022 0:00
A71$8,5071/1/2021 0:00
A71$10,3211/1/2022 0:00
A25$02/1/2021 0:00
A49$2861/1/2021 0:00
A49$01/1/2022 0:00
A88$13,2932/1/2021 0:00
A57$8,3261/1/2021 0:00
A57$8,7991/1/2022 0:00

 

so the total should be 

$85,606

 

 

dreyes6_0-1652407779495.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.