Forum Discussion

dreyes6's avatar
dreyes6
Helper I
4 years ago
Solved

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 ID Name Job ID  About  Date ...
General Comment
  • dreyes6's avatar
    dreyes6
    4 years ago

    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

     

     

     

  • jdbuchanan71's avatar
    jdbuchanan71
    4 years ago

    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 )
)

     

     

  • dreyes6's avatar
    dreyes6
    4 years ago

    Thank you this one works fine!

dreyes6's avatar
dreyes6
Helper I
4 years ago
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?


dreyes6's avatar
dreyes6
Helper I
4 years ago
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