Expiry Balance calculation based on expiry date.

[Anuj_1196]

Name	Expiry Date	Balance
A	1/12/2023	10
A	1/12/2024	20
A	1/12/2025	5
B	1/12/2023	40
B	1/12/2024	50
C	1/12/2023	60
C	1/12/2024	70
C	1/12/2025
D	1/12/2024	90
D	1/12/2025	100
D	1/12/2026	110

Desired Output:

Name	Expiry Date	Balance
A	1/12/2025	5
B	1/12/2024	50
C	1/12/2024	70
D	1/12/2026	110

[FreemanZ]

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:

[Anuj_1196]

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

[v-rzhou-msft · Accepted solution]

Hi @Anuj_1196 ,

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.

[Anuj_1196]

Hi Rico,

I really appreciate your help in solving this puzzle. 

"You are the best"

[FreemanZ]

hi @Anuj_1196 

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: