Calculate sum of invoices for which we received payment.

[ShantanuBakare]

have folloiwng data set

Week No	Region	invoice number	Invoice Type	Bucket	Balance Amount
Wk1	UK	INV001	Overdue	[0-30]	100
Wk1	CE	INV002	Overdue	[46-60]	100
Wk1	UK	INV003	Overdue	[31-45]	100
Wk1	UK	INV004	Overdue	[46-60]	100
Wk1	NA	INV005	Overdue	[0-30]	100
Wk1	CE	INV006	Overdue	[0-30]	100
Wk1	CE	INV007	Overdue	[46-60]	100
Wk1	NA	INV008	Overdue	[31-45]	100
Wk1	CE	INV009	Overdue	[0-30]	100
Wk1	CE	INV010	Overdue	[46-60]	100
Wk2	UK	INV004	Overdue	[31-45]	100
Wk2	CE	INV005	Overdue	[0-30]	100
Wk2	UK	INV006	Overdue	[46-60]	100
Wk2	UK	INV007	Overdue	[31-45]	100
Wk2	NA	INV008	Overdue	[46-60]	100
Wk2	CE	INV009	Overdue	[0-30]	100
Wk2	CE	INV010	Overdue	[0-30]	100
Wk2	NA	INV011	Overdue	[46-60]	100
Wk2	CE	INV012	Overdue	[31-45]	100

I want to calculate Sum of balance amount for invoices which were there in Wk1 and are not in Wk2 (for Ex in above table sum of INV1, INV2 & INV3). Also want sum of newly created invoices in Wk2 such as INV11 & INV12.

I should be able to put it in matrix which will have region in rows or bucket.

[daxer-almighty]

[Inv Amt In WK1\WK2] =
var In_ = "Wk1"
var NotIn_ = "Wk2"
var InvoicesOfInterest =
    EXCEPT(
        CALCULATETABLE(
            DISTINCT( T[Invoice Number] ),
            T[Week No] = In_
        ),
        CALCULATETABLE(
            DISTINCT( T[Invoice Number] ),
            T[Week No] = NotIn_,
            ALL( T )
        )
    )
return
    CALCULATE(
        SUM( T[Balance Amount] ),
        InvoicesOfInterest
    )
    
// The second measure
[Inv Amt in WK2\WK1] =
var In_ = "Wk2"
var NotIn_ = "Wk1"
var InvoicesOfInterest =
    EXCEPT(
        CALCULATETABLE(
            DISTINCT( T[Invoice Number] ),
            T[Week No] = In_
        ),
        CALCULATETABLE(
            DISTINCT( T[Invoice Number] ),
            T[Week No] = NotIn_,
            ALL( T )
        )
    )
return
    CALCULATE(
        SUM( T[Balance Amount] ),
        InvoicesOfInterest
    )

[ShantanuBakare]

Tried creating folloiwng dax but it is not working

PaidInv = CALCULATE(SUM(Sheet1[Balance Amount in USD]),

FILTER(Sheet1,DISTINCT(Sheet1[invoice number])

&&Sheet1[Week No] in {"Wk1"}))