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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.