How to retrieve the latest Balance of the past 12 month 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

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 which i dont want.

Customer Join Month                   Balance

May 2023                                       100 ( May2023)

Apr 2023                                         100  (Apr2023)

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

Customer Join Month                   Latest Balance( Apr2023)

March 2023                                     1000

Apr 2023                                         100

[Mark_Little]

Hi,

I normally approach these kind of thing with flags in the calendar, So i will create a flag for thing i am interested i.e. rolling 12 months .

IF(TempDate <= MONTHSTART(TODAY(1))-1 AND TempDate >= ADDMONTHS(MONTHSTART(TODAY(1)),-12),1) AS F_ROLLING_12_MONTHS,

Then  your set analysis would be 

SUM({$<[New Customer Indicator] = {1}, F_ROLLING_12_MONTHS={1}>}[Balance]).

[eddywong71]

Hi Mark 6505,

Thanks for your reply . However I want to result is like below. No mater which Customer Join Month is ,the latest balance should be May2023(Latest Data).

Customer Join Month                   Latest Balance( May2023)

May 2023                                       100

Apr 2023                                         1000

Base on your reply. The result still like below.

Customer Join Month                   Balance

May 2023                                       100 ( May2023)

Apr 2023                                         100  (Apr2023)