Cross table - Show only the value for the last date by month

[Touix]

Hello Everyone,

I have this data

MONTH,DATE, value

APRIL,15-apr-2020,20

APRIL,16-apr-2020,30

APRIL,16-apr-2020,50

MAY,01-may-2020,10

I want to do a cross table to have the value of the last date per month.

Example :

APRIL,MAY

80,10

I tried:

Sum(
{Date={"$(=Max(Date))"}
VALUE
)

But of course i don't have this value by month, only the max date 😞.  In my table I have only 

MAY

10

In a cross table , how to have the max date value BY MONTH please ?

Thank you for your help and time

[edwin · Accepted solution]

test the max date in your dimension instead of the expression:

create a calculated dimension:  

=if(date(aggr(nodistinct max(Date),Month)) = Date, Date)

in your expression

=sum(VALUE)

[edwin]

the dimension will return the Date if it is equal to the maximum date else null, so set suppress when null

[Touix]

It works perfectly.

Thank you very very much for your help.

So I try to understand.

In my dimension, I choose only the last day of each month by  the aggr

And It picks the date that is equal to one of these dates.

Right ?

[edwin]

yes that is correct, it will cycle through all the dates and any date that is equal to any of the aggregation results will have a value, others will have null.  that is why you need to set suppression on the nulls.

[edwin]

welcome btw