Help with Date Value Expression

[antoniodneto]

Hi Guys,

I'm confused about an expression

I have my date field with format YYYYMM.

I want to returne the value os MAX DATE in a column and MAX DATE - 3.

When I use am KPI to return the dates it works =MAX(DATE) = 201901 and =MAX(DATE,3) = 201811

But when I tried to use on my table to show the measures I can't find the value for -3 months.

To MAX DATE I'm using =sum({<SAFRA_ADESAO={"=$(=max(SAFRA_ADESAO))"}>}VL_OVER30_M3)/1000000

To -3 months =sum({<SAFRA_ADESAO={"=$(=max(SAFRA_ADESAO,3))"}>}VL_OVER30_M3)/1000000

But is not working. Am I forgetting something?

TKS ALL!!!

[sunny_talwar]

May be this

 =Sum({<SAFRA_ADESAO = {"$(=Max(SAFRA_ADESAO,3))"}>} VL_OVER30_M3)/1000000

or this

 =Sum({<SAFRA_ADESAO = {"$(=Date(Max(SAFRA_ADESAO,3), 'YYYYMM'))"}>} VL_OVER30_M3)/1000000

[antoniodneto]

Sunny,

I got using this expression =sum( {< SAFRA_ADESAO={">=$(=Date(AddMonths(Max(SAFRA_ADESAO),-3),'YYYYMM')) <=$(=Date(AddMonths(Max(SAFRA_ADESAO),-3),'YYYYMM'))" }>} VL_OVER30_M3)

Do you think that is an easier way? Any another idea?

[Channa · Accepted solution]

why you doing -3 to get max date

=sum( {< SAFRA_ADESAO={"<=$(=Date(Max(SAFRA_ADESAO),'YYYYMM')) >=$(=Date(AddMonths(Max(SAFRA_ADESAO),-3),'YYYYMM'))" }>} VL_OVER30_M3)

[antoniodneto]

It worked as well. But your expression looks better. Tks again Channa and Sunny!!!!