Link to home
Start Free TrialLog in
Avatar of Scott P
Scott P

asked on

SSRS - How do I get a year value to display based on the current month?

Hi there,

Our company changes fiscal years in Feb each year.  
It works like this:
Fiscal Month = Calendar -1
Fiscal Year = Calendar +1

So, Feb, 2017 = Period 1, 2018.

I need to display this value on the top of reports using the Fiscal Period in SSRS..

Can someone help me with this?  I've attached my attempt at getting this number to show up, but I'm unsure which the best expressions are.

Thanks

=IIF
  (
    DateDiff(DateInterval.Month, Month(Now()), Month(Now())) = "1"
    , Year(Now())
    , DATEADD(DateInterval.Year, +1, Today())
  )

Open in new window

Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Hi Scott,
Please use your expression like this -

--
= " Period " & Month(DATEADD(DateInterval.month, -1 , Today())) & ', ' & YEAR(DATEADD(DateInterval.Year, 1, Today()))
--

Open in new window


For previous month-

Month(DATEADD(DateInterval.month, -1 , Today()))

For next year

YEAR(DATEADD(DateInterval.Year, 1, Today()))

Hope it helps!
Avatar of James0628
James0628

I think that you also want to subtract 1 from the month for the year part, so the year part would be:

YEAR(DATEADD(DateInterval.Year, 1, DATEADD(DateInterval.month, -1 , Today())))

 If you just use the current date, then Jan, 2017 becomes 12, 2018.  If you subtract 1 from the month before adding 1 to the year, then you get 12, 2017.

 James
Avatar of Scott P

ASKER

Thanks for the replies!

Is there a way to add a layer of complexity? The formula that James0628 gave works for now, but next January, the report will likely be broken again..

When it's calendar 01/18, the period/year should be 12/18, but 02/18 should have a period and year of 01/19..  That's why initially I had the IIF in my formula.

Thanks!
SOLUTION
Avatar of James0628
James0628

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Scott P

ASKER

Perfect.

Thank both of you for this!  Ultimately Pawan's answer is best.

Appreciate the time taken, guys!
Glad to help!