locked
Formula to generate financial periods RRS feed

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

    Wednesday, 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