Find the sum of latest Cumulate_User for each account in a Monthly Trend line chart

[abhirup99]

Account	Ref_date	Cumulate_User
Private	1-Jun-18	30
Private	5-Jun-18	28
Private	3-Jul-18	36
Private	10-Jul-18	43
Public	2-Jul-18	20
Public	20-Jul-18	21
Public	27-Jul-18	25
Public	5-Aug-18	21
Public	15-Aug-18	23
Dual	3-Apr-18	12
Dual	20-Apr-18	15
Dual	29-May-18	20
Dual	11-Jun-18	18
Dual	19-Jun-18	19

 

I want to show this in a Monthly Trend line chart where my dimension is "Time_period" which has Month-Year like format Jun-18, May-18 and so on. For reference the Date field to create the dimension "Time_period" is "Ref_date".

So for a particular Month-Year the measure value should include sum of latest "Cumulate_User" for each account against that Month-Year. For example, In Jun-18 the measure value point should be 47 (=28+19), against Jul-18 the measure value point should be 61 (=36+25)and so on for rest of the rest of the Month-Year values. After when a person selects a particular Account from the Filter pane , the value of the measure should shrink down to the latest "Cumulate_User" value against that corresponding account for the particular Month-Year i.e. after the chart is built when I select Account='Private' my Monthly Trend Line chart for Jun-18 should have measure values shrink-ed from 47 to 28 and for Jul-18 and for rest.

Can you please help how to write down the correct Measure expression for the Monthly Trend Line chart as the below expression is not showing the right value when "Time_period" comes into play ??

sum(aggr(FirstSortedValue (Cumulate_User,-Ref_date),Account))

Add tags

[abhirup99]

Account	Ref_date	Cumulate_User
Private	1-Jun-18	30
Private	5-Jun-18	28
Private	3-Jul-18	36
Private	10-Jul-18	43
Public	2-Jul-18	20
Public	20-Jul-18	21
Public	27-Jul-18	25
Public	5-Aug-18	21
Public	15-Aug-18	23
Dual	3-Apr-18	12
Dual	20-Apr-18	15
Dual	29-May-18	20
Dual	11-Jun-18	18
Dual	19-Jun-18	19

[OmarBenSalem · Accepted solution]

try : 

sum(aggr( FirstSortedValue (Cumulate_User,-Ref_Date),Time_Period,Account))

Result:

If I select account = Dual:

If I select Jun 2018 and Jul 2018: