I am not sure why, but the dates are randomly skipping a month, depending on how I edit the second dimension. The data is correct for the remaining months, but I cannot figure out why all previous 13 month end dates aren't appearing.
When I use a list box on the 'PrevMonthEnd' field, all the month ends show including the one that is missing from the table
I think I found out the issue but still need a bit of help resolving it. The months that are missing are those that have month ends on a Sunday(which we carry no data). The months are May2015, and Janurary 2016.
I created a text object with this field
= if(%Date=Date#(MonthEnd(%Date)),1,0) (which is stored as MonthEndFlag) in the data Model.
When I select 1/30/2016, the month end flag = 0. I need to incorporate the logic to say is the date of the month end is a Sunday, get the previous day.
Once again here are the fields from the data model
date(%Date-day(%Date)-(if(WeekDay(%Date-day(%Date))='Sun',1,0))) as PrevMonthEnd,
if(%Date=Date#(MonthEnd(%Date)),1,0) as MonthEndFlag
I would prefer to make a new field for the month end flag with the Sunday logic in it.