topic Re: DAX Measure for YTD Calculation with Condition in DAX Commands and Tips

DAX Measure for YTD Calculation with Condition

Anonymous — Wed, 14 Aug 2024 18:44:01 GMT

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

Re: DAX Measure for YTD Calculation with Condition

Greg_Deckler — Wed, 14 Aug 2024 19:13:56 GMT

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

Re: DAX Measure for YTD Calculation with Condition

Anonymous — Tue, 20 Aug 2024 08:44:13 GMT

Thank you kindly.