Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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'))"}
[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)
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
I would never have found that one alone! Thx!