Creating Year to Date Filter

[Emmy2368]

Hi,

Could you please assist. Currently using BO version 4.1 SP6

I have a task of creating a Year to LastMonthEnd filter for both current & previous year. Our financial year starts from 1st Sept. I'm trying to avoid hardcoding the date in the filter but to make it more dynamic.

DATE Between '2016-09-01 00:00:00.000' AND convert(varchar(10),dateadd(dd,-(day(dateadd(mm,-12,getdate()))-1),dateadd(mm,-12,getdate()-1)),102) + ' 23:59:59.000'
DATE Between '2017-09-01 00:00:00.000' AND convert(varchar(10),dateadd(dd,-(day(getdate())),getdate() ),102)+ ' 23:59:59:999'

I would appreciate your help on this.

Thanks in advance.

 

[hilfy]

I see this has been here a couple of months. Were you able to resolve the issue? If not, I have a couple of thoughts. What type of database are you connecting to?

-Dell

Senior Manager, Data & Analytics
Protiviti

http://www.protiviti.com

 

[Emmy2368]

I wasn't able to solve the problem so I ended up hard-coding it. I'm still very interested on hearing a dynamic way of doing it. I intend to achieve this at Universe level rather than database level. I use SQL server 2012

 

[hilfy]

If you're on SQL Server 2012 or newer, I would do something like this in the universe (based on the current date):

Create a dimension called something like "Current Fiscal Year Start", which would look like this:

Case
  when Month(GetDate()) < 9 then
    DateTimeFromParts(Year(GetDate()) - 1, 9, 1, 0, 0, 0, 0)
  else
    DateFromParts(Year(GetDate(), 9, 1, 0, 0, 0, 0)
end

I would then create another dimension called "End of Today" current date that looks like this:

DateTimeFromParts(Year(GetDate()), Month(GetDate()), Day(GetDate()), 11, 59, 59, 999

If there are specific dates you want to filter this way, I would create a filter for each that uses these two called "<(DateDimension> Fiscal Year to Date".

@Select(Folder\MyDateField) between @Select(Folder\Fiscal Year Start) and @Select(Folder\End of Today)

None of these will validate in the IDT or UDT, but they will work in a query with no issues.

-Dell

Senior Manager, Data & Analytics
Protiviti
[URL unfurl="true"]www.protiviti.com[/URL]

 

[Emmy2368]

Thanks Dell. My apologies for the late response. Your first solution worked for me. Thanks for your assistance. Very much appreciated.

Emma