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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors