DAX Measure for YTD Calculation with Condition

[Anonymous]

Hi All,

I have not been able to find a solution to my below problem.

I have the following table which includes the payment to date information for 4 x claims

Date		Claim_ID	Payment to Date
01/01/2024		498179	1000
01/02/2024		498179	2000
01/01/2024		706401	235
01/02/2024		706401	250
01/01/2024		719069	900
01/01/2024		860121	300
01/02/2024		860121	1800

I would like to build a DAX measure to calculate the YTD SUM of Payments with certain conditions:
1. 2024 Jan should equal the sum of all of the January Payments (2435)

2. 2024 Feb should equal (4950) the sum of all of the updated Payments per ID, plus the January ID - 719069, as it did not have an updated payment. The February total should not sum the previous payment values in the total with exception of 719069 as it did not have an updated payment record in Feb 24'.

The resulting measure should show the below totals when pulled into a matrix, and would be dynamic, to deal with expanded data and the inclusion of Mar', Apr' data etc in the future.

Year	Month	Payment YTD
2024	Jan	2435
2024	Feb	4950

[Greg_Deckler · Accepted solution]

@Anonymous Try:

Measure 2 = 
    VAR __Sum = SUM( 'Table'[Payment to Date] )
    VAR __Month = MONTH( MAX( 'Table'[Date] ) )
    VAR __CurrentClaims = DISTINCT( 'Table'[Claim_ID] )
    VAR __LastMonthClaims = DISTINCT( SELECTCOLUMNS( FILTER( ALL( 'Table' ), MONTH( [Date] ) = __Month - 1 ), "__ClaimID", [Claim_ID] ) )
    VAR __Except = EXCEPT( __LastMonthClaims, __CurrentClaims )
    VAR __Result = __Sum + SUMX( FILTER( ALL( 'Table' ), [Claim_ID] IN __Except ), [Payment to Date] )
RETURN
    __Result

[Anonymous]

Thank you kindly.