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
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())
)
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
YEAR(DATEADD(DateInterval.
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
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!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect.
Thank both of you for this! Ultimately Pawan's answer is best.
Appreciate the time taken, guys!
Thank both of you for this! Ultimately Pawan's answer is best.
Appreciate the time taken, guys!
Glad to help!
Please use your expression like this -
Open in new window
For previous month-
Month(DATEADD(DateInterval
For next year
YEAR(DATEADD(DateInterval.
Hope it helps!