8 Replies Latest reply: Nov 10, 2017 9:59 AM by Daniel Rodríguez

# Working with dates - Last date

Hello guys

First of all thanks for your time.

I have a set of data with measures,dimensions and dates. I've created some charts with dates,measures,etc, but I want to show just the data from the last date.

Lets say, I want to show the sum of A over the max(date). I thought: Well, I can convert a date to number an then get the maximum:

max(num(date))

but it seems that it doesn't work properly. When I try to show the max(num(date)) it throws an error. Am I missing something?

Regards

• ###### Re: Working with dates - Last date

Maybe this will work?  Just adding a set analysis statement to the expression to sum 'A' when the date is the last date.

sum({<[Date] = {'\$(=max(date([Date])))'}>}[A])

• ###### Re: Working with dates - Last date

That's great,thank you!!!

I've used that expression and it seems to be working as expected but there is a little issue: I don't want to be affected by any of the filters I have, so I wrote this:

sum({1<[Date] = {'\$(=max(date([Date])))'}>}[A])

Unfortunately, if a filter by date, this value is affected by the date I choose. Is there any way to avoid that?

Regards

• ###### Re: Working with dates - Last date

hmmmm... You can try this:

Define a variable (for instance vMaxDate), as:

=max(date([Date]))

Then try:

sum({1<[Date] = {'\$(vMaxDate)'}>}[A])

• ###### Re: Working with dates - Last date

It's still not working...It's odd because the only filter affect that expression is an string made with year+month, but this string is not made using date, it's made in the script.

I mean

Select date,year(date),month(date),....... from ##blablabla

What do you think?

• ###### Re: Working with dates - Last date

Sum({<Date = {'\$(=Max(Date ))'}>} A ))

Maybe above one?

• ###### Re: Working with dates - Last date

Might work.  Not sure of your date structure.