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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.