How to retrieve the latest Balance from the new join customer

[eddywong71]

Hi All,

I want to retrieve the past 12 month new join customer with latest balance at line chart. For better understand , showed as below table.

Customer Join Month	Latest Balance( May2023)
May 2023	100
Apr 2023	1000
March 2023	2000

I am using the expression in line chart  like below.

Dimension:

= Date([Month], 'MMM YY')

Expression

SUM({$<[New Customer Indicator] = {1}, 
Month = {"$(='>' & Date(MonthStart(Max(Month),-12)) & '<=' & Date(Max(Month)))"}>}[Balance]).

However this Expression will retrieve the data like below table

Customer Join Month	Balance
May 2023	100 (May 2023)
Apr 2023	50    (Apr 2023)
March 2023	100 (March 2023 )

.

Also i would like the user select the latest Balance Month For example:  Month select (Apr2023)

Customer Join Month	Latest Balance( Apr2023)
Apr 2023	1000
March 2023	2000
Feb 2023	3000