Filter Year via Script

[hammermill21]

Hello,

I am pulling in data and have it set to bring in only data starting from 01/01/2016:

[UMH Central Line Surveillance]:

load * where [Created Date]>='2016-01-01 00:00';

which is perfect, but now when I create a filter for the month/year I get all the years all the way up to 2024. How can I remove that?

Thank you!

[sunny_talwar]

What Years do you want to get? May be do this

[UMH Central Line Surveillance]:

load * where [Created Date]>='2016-01-01 00:00' and [Created Date] <= Today();

[hammermill21]

Hey Sunny!

I just want it to go up this year and current month.

I tried what you said but it still is doing the same thing. Could it be in my Measures that I created? I have this:

Date([Created Date.autoCalendar.Month],'MMM YYYY')

[Anil_Babu_Samineni]

Could you elaborate more on this? I've read last response as well.

[hammermill21]

Hi Anil,

I have a filter with the Month/Year starting from Jan 2016 but it is going all the way up to the year 2024. I only have data for last year and this year. I want to be able to have my filter only should last year and this year.

Make sense?

[Anonymous]

Do you have a Master Calendar going up to 2024 in your data model ?

[hammermill21]

I do have a master calendar and I don't think it is going till 2024. This is what I have in the script:

MinMaxDate:

Load

  min([Get Date]) as MinDate,

    max([Get Date]) as MaxDate

resident [UMH Central Line Surveillance];

Let vMinDate = Peek('MinDate',0,'MinMaxDate'); 

Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate');

Let vToday = $(vMaxDate);

drop table MinMaxDate;

TempCal:

Load

  date($(vMinDate) + rowno()-1) as TempDate

autoGenerate($(vMaxDate)-$(vMinDate)+1);

Mastercalendar:

Load

  TempDate as [Get Date],

    year(TempDate) as Year,

    month(TempDate) as Month,

    monthname(TempDate) as MonthYear,

    day(TempDate) as Day,

    weekday (TempDate) as WeekDay,

    week(TempDate) as Week,

    year(yearstart(TempDate,0,4)) as FiscalYear,   

  'Q' & ceil(month(TempDate)/3) as Quarter,      /

  InYearToDate(TempDate,$(vToday),0) * -1 as CurYTDFlag,

  InYearToDate(TempDate,$(vToday),-1) * -1 as LastYTDFlag

resident TempCal;

drop table TempCal;

[Monthly Inspection Status Temp]:

LOAD

  Date(AddMonths($(vMinDate),IterNo()-1),'MMM YYYY' ) AS [Inspection Month],

    Max([Get Date]) as [Max Date]

RESIDENT [UMH Central Line Surveillance]

While AddMonths($(vMinDate),IterNo()-1) <= $(vMaxDate);

DROP Field [Max Date] FROM [UMH Central Line Surveillance];