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!

jdbuchanan71's avatar
jdbuchanan71
Super User
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
    Helper I
    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