Assigning Dates to Fiscal Year

Assigning Dates to Fiscal Year

Here are some alternatives. They all return numeric years but if you really need a string starting with FY then use paste0("FY", result) where result is any of the results below. They all support vector input, i.e. the input dates can be a vector.

1) zoo::as.yearmon The zoo package has a "yearmon" class which represents year/months as year + fraction where fraction = 0 for jan, 1/12 for feb, 2/12 for march and so on.

Using that this one-liner will do it. It subtracts 4/12 (since April is end of year) and adds 1 (i.e. add one year). Then to get the year take the integer part:

library(zoo)

as.integer(as.yearmon(dates) - 4/12 + 1)
## [1] 2016 2015 2015

2) POSIXlt Here is a solution that does not use any packages. Convert the dates to POSIXlt class. It's mo component represents Jan as 0, Feb as 1, etc. so if we are May or later (mo is 4 or more) then the fiscal year is the following calendar year otherwise it is the current calendar year. The year component of POSIXlt objects is the number of years since 1900 so add the year to 1900 plus 1 if we are at May or later:

lt <- as.POSIXlt(dates)
lt$year + (lt$mo >= 4) + 1900
## [1] 2016 2015 2015

3) format Add the year to 1 if the month is greater than or equal to 5 (or to zero if not). This also uses no packages:

as.numeric(format(dates, "%Y")) + (format(dates, "%m") >= "05")
## [1] 2016 2015 2015

4) substr. We can extract the year using substr, convert to numeric and add 1 if the extracted month (also extracted usingsubstr) is "05" or greater.; Again no packages are used.

as.numeric(substr(dates, 1, 4)) + (substr(dates, 6, 7) >= "05")
## [1] 2016 2015 2015

5) read.table This also uses no packages.

with(read.table(text = format(dates), sep = "-"), V1 + (V2 >= 5))
## [1] 2016 2015 2015

Note: We used this as the input dates:

dates <- as.Date(c("2015-05-01", "2015-04-30", "2014-09-01"))

Assign Fiscal Year based on data ranges

Looks like your fiscal year begins two months before the calendar year, so I'll add two months to the current date and take the year property

offset = pandas.DateOffset(months=2)
df['Fiscal Year'] = (df['Date Generated'] + pandas.DateOffset(months=2)).dt.year

Obtaining a list of all fiscal years between two dates

Define a function, fyear, that converts a date to a fiscal year by converting to yearqtr class and adding 1/4 which pushes Oct/Nov/Dec to the next calendar year so that the calendar year of that is the required fiscal year. Finally apply it to each row by grouping by id.

library(dplyr)
library(zoo)

fyear <- function(...) as.integer(as.yearqtr(...) + 1/4)

dat %>% 
  group_by(id) %>% 
  group_modify(~ data.frame(fyear = fyear(.$start_dt):fyear(.$end_dt) %% 100)) %>%
  ungroup

giving:

# A tibble: 5 x 2
     id fyear
  <dbl> <dbl>
1     1    17
2     1    18
3     1    19
4     2    17
5     2    18

Get the financial year from a date in a pandas dataframe and add as new column

This will work, assuming your date column is base_date and is a datetime object:

df['financial_year'] = df['base_date'].map(lambda x: x.year if x.month > 3 else x.year-1)

adjust x axis dates to match start of the ( fiscal ) year

Here is a not-too-pretty solution. The logic somewhat follows your own: find the starting date/time for each fiscal year (March 1 = time 1) and the last date/time (Feb 28 = time 365). Use this separate 'time' variable as your x-axis, then re-label the tick marks. You can change the scale_x_continuous() breaks and labels to get your desired dates along the x-axis.

t <- data.frame(date=seq.Date(as.Date('2018-03-01'),as.Date('2020-02-28'),by='days'),
                fy=1)
t$fy[t$date>='2019-03-01'] <- 2
t <- t %>% group_by(fy) %>% mutate(time=seq(1:n()))

dat <- left_join(dat,t) 
dat %>% ggplot(.) +
  geom_path(aes(x = time, y = value, color = factor(fy),group=fy)) +
  scale_x_continuous(breaks = c(1,100,200,300),labels=c('March 1','June 8','Sept 16','Dec 25'))

Calculate Fiscal Year in Snowflake

This should do what you want :

select case when quarter(current_date()) > 2 
         then date_from_parts(year(current_date()), 07, 01)
         else date_from_parts(year(current_date()) -1, 07, 01)
       end as Fiscalyearbegins
      ,case when quarter(current_date()) > 2 
         then date_from_parts(year(current_date()) +1, 06, 31)
         else date_from_parts(year(current_date()) ,06, 31)
       end as FiscalyearEnds

Logic to get dynamic date range in query for Fiscal Year

If your fiscal year is already in the format, then you can use the following and condition:

FISCAL_YEAR = CASE WHEN MONTH(@PPE) BETWEEN 1 AND 6 THEN YEAR(@PPE)
                      ELSE YEAR(@PPE)+1 END

The above condition should work under the assumption that, Jul 2019 to Jun 2020 is called as Fiscal year 2020, as per your data. Hope this helps.

Most effective way to group data in quarters and fiscal years in R

Not sure if this was available at the time but the lubridate package contains a quarter function which allows you to create your fiscal quarter and year columns.

The documentation is here.

Examples for your case would be:

x <- ymd("2011-07-01")
quarter(x)
quarter(x, with_year = TRUE)
quarter(x, with_year = TRUE, fiscal_start = 7)

You can then use dplyr and paste function to mutate your own columns in creating fiscal quarters and years.

---

Related Topics