YTD Flag Question

[Anonymous]

Hello,

In my script editor I have a code that works perfectly fine, but I am looking to make an update to it.  Sometimes when I am working on my data, the lag between when the data comes in is more than one month, and sometimes it is not even a month.  Currently I am basing my YTD off of addmonths(Today()-1).  Code is below:

InYearToDate(NewDate, addmonths(today(),-1) ,-1) *-1 as CalendarPYTD,

I would like to change it something related to date(max(NewDate)) or

date#(MakeDate($(vBusinessYear),max({<CalendarYear={$(=max(CalendarYear))}>} CalendarMonth),1)).  In both cases that would properly return 02 for Feb.

However each time the code fails.  Any thoughts on how to best update this code so it is based off of the latest month in the Current Year of my data set?

Thank you in advance!

Justin

[sunny_talwar · Accepted solution]

May be like this

Fact:

LOAD Date,

          ....

FROM Fact;

MaxDate:

LOAD Max(Date) as MaxDate

Resident Fact;

LET vMaxDate = Peek('MaxDate');

DROP Table MaxDate;

Calendar:

LOAD ...,

          InYearToDate(NewDate, addmonths($(vMaxDate), -1) ,-1) *-1 as CalendarPYTD,

[Anonymous]

Thank you for the help, that works very well.