topic Re: Expiry Balance calculation based on expiry date. in DAX Commands and Tips

Expiry Balance calculation based on expiry date.

Anonymous — Thu, 25 May 2023 06:50:54 GMT

Desired Output:

FreemanZ — Thu, 25 May 2023 06:07:49 GMT

hi Anonymous

try to create a calculated table like:

Table = ADDCOLUMNS( ADDCOLUMNS( VALUES(data[Name]), "Expiry Date", CALCULATE(MAX(data[Expiry Date])) ), "Balance", CALCULATE(MAX(data[Balance])) )

it worked like:

FreemanZ — Thu, 25 May 2023 06:15:32 GMT

or you plot a table visual with the name column and two measures like:

ExpiryDate = MAX(data[Expiry Date])

_Balance = MAXX( FILTER( data, data[Expiry Date]=[ExpiryDate] ), data[Balance] )

it worked like:

Anonymous — Thu, 25 May 2023 06:49:59 GMT

Thanks for your time. I'm using similar formula, unfortunately it's giving me the sum rather than the balance on that particular year.

Anonymous — Tue, 30 May 2023 08:02:17 GMT

Hi Anonymous ,

You can create measures and show your result in a table visual.

Last Expire Date = MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Balance] <> BLANK () && 'Table'[Name] = MAX ( 'Table'[Name] ) ), 'Table'[Expiry Date] )

Balance on Last Expire Date = CALCULATE ( SUM ( 'Table'[Balance] ), FILTER ( 'Table', 'Table'[Expiry Date] = [Last Expire Date] ) )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous — Wed, 31 May 2023 01:41:14 GMT

Hi Rico,

I really appreciate your help in solving this puzzle.

"You are the best"