Monthly sum of last available date

[Anonymous]

Hello,

I got data that look like this:

DATE	COUNTRY	CUSTOMER BASE DAILY
2018-01-30	US	0
2018-01-30	AU	1
2018-01-31	US	1
2018-01-31	AU	1
2018-02-27	US	5
2018-02-27	AU	1
2018-02-28	US	5
2018-02-28	AU	1
2018-03-29	US	6
2018-03-29	AU	1
2018-03-30	US	7
2018-03-30	AU	2
2018-04-13	US	7
2018-04-13	AU	3
2018-04-15	US	8
2018-04-15	AU	3

and I'd like to create a table where the dimension is the month and measure is the sum of the customer base column of the most recent date of that month: for the past month it, usually, coincides with the last day of the month but for the current month it must show the most recent available values.

So the table above should generate a table like this one:

MONTH	CUSTOMBER BASE
2018-01	2
2018-02	6
2018-03	9
2018-04	11

Could you help me with this?

Thanks,

Yari

[Thiago_Justen_]

Do you have a field called Month (mm format)?

Considering a negative answer I'd suggest you:

As dimension:

Date (DATE,'YYYY-MM')

As expression:

Sum(Aggr(If(DATE=Max(Total DATE),[CUSTOMER BASE]),Date (DATE,'YYYY-MM')))

[Anonymous]

Thanks Thiago for your answer, but unfortunately it doesn't work.

To give you some more info: I do have a [DATE.autoCalendar.YearMonth] field, which it's the one I'm using as dimension. I tried using the formula you provided "as is" and also changing Date (DATE,'YYYY-MM') to use [DATE.autoCalendar.YearMonth] but with no results: in both cases I get all values equal to 0.

Yari

[Thiago_Justen_]

Are you able to share an example app? 

[Anonymous]

Please find the test app attached: I included both widgets using the solution you proposed and another one using the autocalendar YearMonth field.

Best Regards,

Yari