Skip to Content
0
Former Member
Feb 25, 2011 at 03:26 PM

Value & date measure problem

28 Views

Morning,

I am having an issue and would like to see if anyone could help me out...Here is my problem.

I have a prior YTD measure which is a sum of all of 2010 in a $ amount. What I am trying to do is return a value from this prior YTD figure that takes into account todays date with 'Calendar Month'. I am very close to solving this but I have run into a problem. My formula:

=[Prior YTD Figure] Where ([L01 Calendar Month]<= FormatNumber(MonthNumberOfYear(CurrentDate());"#"))

so for example when this forumula is run today in a WEBI, it returns values for not only January and Februray which it should but it also returns values for October, November and December which it should not. I have a temporary fix;

=[Prior YTD Figure] Where ([L01 Calendar Month]<= FormatNumber(MonthNumberOfYear(CurrentDate());"#")) And [L01 Calendar Quarter] InList ("1";"2";"3"))

Can anyone recommend a solution where i dont have to rely on my temporary fix? In a way to automate the forumula?

When broken down in the WEBI it should look like this(because todays month is Februay):

calendar month1 value

calendar month2 value

instead it looks like this:

calendar month1 value

calendar month10 value

calendar month11 value

calendar month12 value

calendar month2 value

Thanks,

Thomas

Edited by: Carter Russell on Feb 25, 2011 9:26 AM

Edited by: Carter Russell on Feb 25, 2011 9:30 AM