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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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

Top Solution Authors