Date functions in set analysis

[maxim1500]

Hello,

I am trying to filter out a dataset to keep only the last day of the current year (based on my max snapshot date selection).

I am expecting something like this to work, based on what I saw on this forum:

[Snapshot Date] = {"$(=MonthEnd(YearStart(max([Snapshot Date]))))"}

If I do =MonthEnd(YearStart(max([Snapshot Date]))) in a KPI, I get the right date.

Also, If I execute this function, it works:

Max({<[Snapshot Date] = {"$(=min([Snapshot Date]))"}>}[Snapshot Date])

But as soon as I add another function (like MonthEnd, AddMonths, ...), I only get "-" as a result.

Any idea?

Thank you!

[sunny_talwar]

Is your Snapshot Date formatted as a number? If it is, try this:

[Snapshot Date] = {"$(=Num(MonthEnd(YearStart(max([Snapshot Date])))))"}

or if it is date, then this may be:

[Snapshot Date] = {"$(=Date(MonthEnd(YearStart(max([Snapshot Date]))), 'DateFieldFormatHere'))"}

[maxim1500]

[Snapshot Date] is a number when I output it in a kpi with auto format.

Max({<[Snapshot Date] = {"$(=Num(MonthEnd(YearStart(max([Snapshot Date])))))"}>}[Snapshot Date]) -> Doesn't work

Max({<[Snapshot Date] = {"$(=Num(MonthEnd(max([Snapshot Date]))))"}>}[Snapshot Date]) -> Doesn't work

Max({<[Snapshot Date] = {"$(=Num(max([Snapshot Date])))"}>}[Snapshot Date]) -> works fine.

MonthEnd should work just fine as all dates are end of month. Max(date) = '2013-12-31' and '2013-01-31' is there.

As a mather of fact:

Max({<[Snapshot Date] = {"$(=Num(min([Snapshot Date])))"}>}[Snapshot Date])   returns 41305 (2013-01-31)

[sunny_talwar · Accepted solution]

I see what the issue is, try this now:

[Snapshot Date] = {"$(=Num(Floor(MonthEnd(YearStart(max([Snapshot Date]))))))"}

MonthEnd gives a timestamp such as 01/31/2016 11:59:59 PM which won't match you date. By adding a Floor you will get ride of the time portion

[maxim1500]

I would never have found that one alone! Thx!