Forum Discussion

eryka_90's avatar
eryka_90
Icon for Helper I rankHelper I
2 years ago

Sum Unique value based on date and week

Hello Community,

 

How to have sum of amount by Unique ID considering max load date for particular load week.

Example as below

 

ID No  Load DateLoad Week Amount
1234 30/10/2023 44 3,500 
1234 04/11/2023 44 3,500 
5678 24/10/2023 43 2,300 
3451 30/10/2023 44 4,000
123425/9/2023423,500 

 

The chart should display Total amount Week 44 = 7,500 Week 43 = 2,300 and Week 42 = 3,500.

I use below formula but for Week 44 its sum as 11,000
 
TotalAmount = SUMX(DISTINCT('Pending Items'[CORA ID]),CALCULATE(SUM('Pending Items'[Invoice Amount (USD)])))
 
Thank in advance for your help!
Need Help
Tips and Tricks

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    eryka_90  Does this work for you?

    TotalAmountByLoadWeek =
    CALCULATE (
        SUM ( 'Sheet1'[Amount] ),
        FILTER (
            Sheet1,
            'Sheet1'[Load Date]
                = CALCULATE (
                    MAX ( 'Sheet1'[Load Date] ),
                    ALLEXCEPT ( 'Sheet1', 'Sheet1'[Load Week] )
                )
        )
    )

     



    • eryka_90's avatar
      eryka_90
      Icon for Helper I rankHelper I
      2 years ago

      Hi Anonymous ,

       

      The formula given has weird value :

      As my previous formula TotalAmount = SUMX(DISTINCT('Pending Items'[CORA ID]),CALCULATE(SUM('Pending Items'[Invoice Amount (USD)]))), its give right value but only for week which have duplicate data will calculate sum too. For week 44, its should display 1,586.