max date and max -1

[yogitamehta10]

HI All,

I'm getting date added in my sheet ramdomly.

i want to calculate sum of  sales where date  is maximum date and  1 less from maximum ,

so latest date and last lastest date.

how should i define it.

22/04/2016

28/04/2016

01/05/2020

[sunny_talwar]

May be like this:

Max Date

Sum({<Date = {"$(=Date(Max(Date), 'DD/MM/YYYY))"}>} Sales)

Max Date - 1

Sum({<Date = {"$(=Date(Max(Date, 2), 'DD/MM/YYYY))"}>} Sales)

Max Date and Max Date - 1

Sum({<Date = {"$(=Date(Max(Date, 2), 'DD/MM/YYYY))", "$(=Date(Max(Date), 'DD/MM/YYYY))"}>} Sales)

[avinashelite · Accepted solution]

try like this

sum({<date_field={"=max({1}date_field)"}>}sales)

sum({<date_field={"=Date(max({1}date_field)-1,'DD-MM-YYYY')"}>}sales)

NOTE : change the 'DD-MM-YYYY' format according to your date format

[yogitamehta10]

Hi,

It is working but showing result of max +max-1 .

sum({<startDate={"=Date(max({1}startDate)-1,'DD/MM/YYYY')"}>}data)

i'm using this for last max -1 

[yogitamehta10]

Hi Avinash,

This is not working perfectly .

sum({<date_field={"=Date(max({1}date_field)-1,'DD-MM-YYYY')"}>}sales)

this is giving result as sum of max and max-1 .

[yogitamehta10]

Hi sunny,

Max date-1 is not working .

[sunny_talwar]

Try this:

Sum({<date_field={"$(=Date(Max({1}date_field)-1,'DD-MM-YYYY'))"}>}sales)

[yogitamehta10]

Hi Sunny,

This is not working. Could you please help with something?

[sunny_talwar]

Would you be able to share a sample where this isn't working for you?

[yogitamehta10]

ok.. Please check