Sum Unique value based on date and week

[eryka_90]

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 Date	Load 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
1234	25/9/2023	42	3,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!

[Anonymous]

@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]

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.