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

Answer 1

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)))).ToString

Please 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 Support

Answer 2

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

Answer 3

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

Answer 4

Amazing, thanks!

---

Suzi