Answered by:
Formula to generate financial periods

Question
-
Hello,
Does anyone have a clever formula to work out the financial period as a calculated field in Report Builder? I have one for week numbers and wondering if there is something similar for periods too. We work on a 4,4,5 week cycle (I've been told it's financial but may be wrong) so period 1 is 4 weeks, week no 1-4, period 2 is 4 weeks, week no 5-8 and period 3 is 5 weeks, week no 9-13.
My week formula looks like: -
="Week" + format(Datepart(Dateinterval.weekofyear,Fields!JournalCreatedDate.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek)).ToString()
Also if there is a way to get the year displayed (something like 2013P1) that would be amazing!
Thanks
Suzi
Tuesday, April 16, 2013 2:36 PM
Answers
-
Hi Suzi,
Sorry for the delay.
Change the expression to:
=format(Year(parameters!Date.Value)).ToString+"P"+format(iif((Datepart(Dateinterval.weekofyear,parameters!Date.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek))=53,"12",iif(((Datepart(Dateinterval.weekofyear,parameters!Date.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek)) mod 13)=0,Floor((Datepart(Dateinterval.weekofyear,parameters!Date.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek))/13)*3,iif(Floor((Datepart(Dateinterval.weekofyear,parameters!Date.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek)) mod 13)<=4,Floor((Datepart(Dateinterval.weekofyear,parameters!Date.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek))/13)*3+1,iif(Floor((Datepart(Dateinterval.weekofyear,parameters!Date.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek)) mod 13)<=8,Floor((Datepart(Dateinterval.weekofyear,parameters!Date.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek))/13)*3+2,Floor((Datepart(Dateinterval.weekofyear,parameters!Date.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek))/13)*3+3))))).ToString()Regards,
Charlie Liao
TechNet Community Support- Marked as answer by De Dancey Wednesday, April 24, 2013 11:02 AM
Tuesday, April 23, 2013 7:28 AM
All replies
-
Hi Suzi,
I have tested it on my local environment, we can use the expression below:
=format(Year(parameters!Date.Value)).ToString+"P"+format(iif(((Datepart(Dateinterval.weekofyear,parameters!Date.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek)) mod 13)=0,Floor((Datepart(Dateinterval.weekofyear,parameters!Date.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek))/13)*3,iif(Floor((Datepart(Dateinterval.weekofyear,parameters!Date.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek)) mod 13)<=4,Floor((Datepart(Dateinterval.weekofyear,parameters!Date.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek))/13)*3+1,iif(Floor((Datepart(Dateinterval.weekofyear,parameters!Date.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek)) mod 13)<=8,Floor((Datepart(Dateinterval.weekofyear,parameters!Date.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek))/13)*3+2,Floor((Datepart(Dateinterval.weekofyear,parameters!Date.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek))/13)*3+3)))).ToStringPlease change the ”parameters!Date.Value” to you date in the expression.
If you have any questions, please feel free to ask.
Regards,
Charlie Liao
TechNet Community SupportWednesday, April 17, 2013 3:29 AM -
Hi Charlie,
This is amazing, but one slight problem (and this is my own fault, I should have clarified further) once in a blue moon we get 53 weeks in a year instead of 52 (I think normally when there is a leap year). The above works perfectly apart from last year being a 53 week year. The whole of that week needs to be stuck into P12, currently it is displaying as 2012P13 and 2013P13 and be classed as 2012... Is there any way to take this into account?
If it makes it easier I can probably have the year part stripped out so it just displays P1,P2 etc and do the year part in another calculated field.
Thanks
Suzi
Suzi
Wednesday, April 17, 2013 10:01 AM -
Hi Suzi,
Sorry for the delay.
Change the expression to:
=format(Year(parameters!Date.Value)).ToString+"P"+format(iif((Datepart(Dateinterval.weekofyear,parameters!Date.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek))=53,"12",iif(((Datepart(Dateinterval.weekofyear,parameters!Date.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek)) mod 13)=0,Floor((Datepart(Dateinterval.weekofyear,parameters!Date.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek))/13)*3,iif(Floor((Datepart(Dateinterval.weekofyear,parameters!Date.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek)) mod 13)<=4,Floor((Datepart(Dateinterval.weekofyear,parameters!Date.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek))/13)*3+1,iif(Floor((Datepart(Dateinterval.weekofyear,parameters!Date.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek)) mod 13)<=8,Floor((Datepart(Dateinterval.weekofyear,parameters!Date.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek))/13)*3+2,Floor((Datepart(Dateinterval.weekofyear,parameters!Date.Value,FirstDayOfWeek.Sunday,FirstWeekOfYear.FirstFullWeek))/13)*3+3))))).ToString()Regards,
Charlie Liao
TechNet Community Support- Marked as answer by De Dancey Wednesday, April 24, 2013 11:02 AM
Tuesday, April 23, 2013 7:28 AM -
Amazing, thanks!
Suzi
Wednesday, April 24, 2013 11:03 AM