Adding fiscal month end

0
votes

I would like to mutate a fiscal month-end date to a dataset in R. In my company the fiscal month-end would be on 21st of that. For example

12/22/2019 to 1/21/2020 will be Jan-2020 1/22/2020 to 2/21/2020 will be Feb-2020 2/22/2020 to 3/21/2020 will be Mar-2020 etc

Dataset

enter image description here

Desired_output

enter image description here

How would I accomplish this in R. The Date column in my data is %m/%d/%Y(1/22/2020)

2
rdatelubridate
Please add first few rows of your data using dput and show expected output for that. dput(head(df)). – Ronak Shah
@RonakShah just added – Sri
Please see the instructions for posting at the top of the r tag page. If you post images then no one can use the data except by tediously retyping it. Showing the output of dput(x) or dput(head(x)) in your question avoids this problem. – G. Grothendieck

2 Answers

0
votes

You could extract the date and if date is greater than 22 add 10 days to it and get the date in month-year format :

transform(dat, Fiscal_Month = format(Date + 
               ifelse(as.integer(format(Date, '%d')) >= 22, 10, 0), '%b %Y'))

#        Date Fiscal_Month
#1 2020-01-20     Jan 2020
#2 2020-01-21     Jan 2020
#3 2020-01-22     Feb 2020
#4 2020-01-23     Feb 2020
#5 2020-01-24     Feb 2020

This can also be done without ifelse like this :

transform(dat, Fiscal_Month = format(Date + c(0, 10)
                        [(as.integer(format(Date, '%d')) >= 22) + 1], '%b %Y'))

data

Used this sample data :

dat <- data.frame(Date = seq(as.Date('2020-01-20'), by = '1 day',length.out = 5))
0
votes

1) yearmon We perform the following steps:

  • create test data d which shows both a date in the start of period month (i.e. 22nd or later) and a date in the end of period month (i.e. 21st or earlier)
  • convert the input d to Date class giving dd
  • subtract 21 days thereby shifting it to the month that starts the fiscal period
  • convert that to ym of yearmon class (which represents a year and a month without a day directly and internally represents it as the year plus 0 for Jan, 1/12 for Feb, ..., 11/12 for Dec) and then add 1/12 to get to the month at the end of fiscal period.
  • format it as shown. (We could omit this step, i.e. the last line of code, if the default format, e.g. Jan 2020, that yearmon uses is ok.

The whole thing could easily be written in a single line of code but we have broken it up for clarity.

library(zoo)

d <- c("1/22/2020", "1/21/2020") # test data

dd <- as.Date(d, "%m/%d/%Y")
ym <- as.yearmon(dd - 21) + 1/12
format(ym, "%b-%y")
## [1] "Feb-20" "Jan-20"

2) Base R This could be done using only in base R as follows. We make use of dd from above. cut computes the first of the month that dd-21 lies in (but not as a Date class object) and then as.Date converts it to one. Adding 31 shifts it to the end of period month and formatting this we get the final answer.

format(as.Date(cut(dd - 21, "month")) + 31, "%b-%y")
## [1] "Feb-20" "Jan-20"