Fiscal Year To Date

SSAS and SSRS
1

So the Fiscal Year for this company is Apr - Mar
They would like to see MTD and YTD from last fiscal year and this fiscal year for comparison reporting.

[code]
= (CASE WHEN MONTH(GETDATE()) < 3 THEN DATEFROMPARTS(YEAR(GETDATE())-1 , 3 , 31) ELSE DATEFROMPARTS(YEAR(GETDATE()) , 3 , 31) END)

= (CASE WHEN MONTH(GETDATE()) < 3 THEN DATEFROMPARTS(YEAR(GETDATE()) , 3 , 31) ELSE DATEFROMPARTS(YEAR(GETDATE()) + 1 , 3 , 31) END)[/code]

Unfortunately the above code does not work in 2008
What's the code to do the same in 2008?

2 
select 
    (CASE WHEN MONTH(GETDATE()) < 3 THEN CAST(CAST(YEAR(GETDATE())-1 AS VARCHAR(4))+'0331' AS DATETIME) 
      ELSE CAST(CAST(YEAR(GETDATE()) AS VARCHAR(4))+'0331' AS DATETIME) END) as dt_1
,(CASE WHEN MONTH(GETDATE()) < 3 THEN CAST(CAST(YEAR(GETDATE()) AS VARCHAR(4))+'0331' AS DATETIME) 
      ELSE CAST(CAST(YEAR(GETDATE())+1 AS VARCHAR(4))+'0331' AS DATETIME) END) as dt_2
dt_1 dt_2
31/03/2018 00:00:00 31/03/2019 00:00:00
3

thank you

Perhaps the code I had is incorrect.
I tried what you've shared and I'm only getting March 31, 2018 data.

Need MTD and YTD from LAST fiscal year and THIS fiscal year, rolling forward.

So it should return
4/1/2017 - 4/16/2017 AND 4/1/2018 - 4/16/2018 today. and so on
where
next month, say on the 11th, it should return
5/1/2017-5/10/2017 AND 5/1/2018-5/10/2018 for MTD
AND 4/1/2017-5/10/2017 plus 4/1/2018-5/10/2018
and continue through the end of the fiscal year then repeat but next Apr it'll show 2018 and 2019, etc ...

4

Hi,

What you posted initially it's using GETDATE().
Add some input data and what it's the desired output.

5

? DateFromParts does not work in 2008

How about this, what's the correct syntax for:
Last year - so something like DateAdd(yyyy, -1, Getdate())?
Last year Month to be - 4/1/previousyear using the above for the year to date so 4/18/previous year
This year - so DateAdd(yyyy, 0, Getdate())
current month to date - DATEADD(MONTH, DATEDIFF(MONTH, 0, GetDate()-1), 0) this provides current month to date

6

It could also be because I am using it against:

CASE WHEN { fn DAYOFWEEK(dbo.SO_SalesOrderHistoryHeader.OrderDate) } IN (1 , 7) THEN dbo.DAYSADDNOWK(OrderDate , 1) ELSE DATEADD(dd , 0 , DATEDIFF(dd , 0 , dbo.SO_SalesOrderHistoryHeader.OrderDate)) END

7

This will get you the start of current month:

dateadd(month,datediff(month,0,current_timestamp),0)

This will get you the start of current fiscal year:

dateadd(year,datediff(month,90,current_timestamp)/12,90)
1 Like
8

This is giving what I looking for as far as MTD last year (LMTD) and MTD this year
Is there's a way to run this

>= DATEADD(YEAR, - 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AND [highlight #FCE94F]< DATEADD(YEAR, - 1, GETDATE() - 1)[/highlight]
>= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND [highlight #FCE94F]< GETDATE() - 1[/highlight]

against this?

CASE WHEN { fn DAYOFWEEK(dbo.SO_SalesOrderHistoryHeader.OrderDate) } IN (1 , 7) THEN dbo.DAYSADDNOWK(OrderDate , 1) ELSE DATEADD(dd , 0 , DATEDIFF(dd , 0 , dbo.SO_SalesOrderHistoryHeader.OrderDate)) END

And then a way to get 4/1/lastyear to date last year and 4/1/thisyear to date in the current year as we go into other months?
Meaning next month will have 4/1/201# - May, etc ... in both years?
Set it to always start on 4/1/#### since that's the start of the fiscal year.

9

Thanks! dateadd(year,datediff(month,90,current_timestamp)/12,90)

I'm having trouble modifying that for last year, apparently this isn't correct

dateadd(year,-1, datediff(month,90,current_timestamp)/12,90)

10

Change this:

to this:

dateadd(year,-1 + datediff(month,90,current_timestamp)/12,90)
1 Like
11

Excellent! Thank you so much!

If there's a way to always get LMTD and MTD out of LYTD and YTD?
They want to see LYTD and YTD AND LMTD and MTD

12

Thanks again!
Here's the full, in case someone else needs it like I did

Last Fiscal Year beginning April where weekends are marked as Monday = (CASE WHEN { fn DAYOFWEEK(dbo.SO_SalesOrderHistoryHeader.OrderDate) } IN (1, 7) THEN dbo.DAYSADDNOWK(OrderDate, 1) ELSE DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.SO_SalesOrderHistoryHeader.OrderDate)) END BETWEEN DATEADD(year, - 1 + DATEDIFF(month, 90, GETDATE()) / 12, 90) AND DATEADD(YEAR, - 1, GETDATE() - 1)) 
OR
This Fiscal Year beginning April where weekends are marked as Monday = (CASE WHEN { fn DAYOFWEEK(dbo.SO_SalesOrderHistoryHeader.OrderDate) } IN (1, 7) THEN dbo.DAYSADDNOWK(OrderDate, 1) ELSE DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.SO_SalesOrderHistoryHeader.OrderDate)) END BETWEEN DATEADD(year, DATEDIFF(month, 90, GETDATE()) / 12, 90) AND GETDATE() - 1)