How to Adjust the Date According to Fiscal Period starting from March?
Let’s say your company’s fiscal period starts from March and ends at the end of February. Now, you want to aggregate the revenue for each finanical year like below.
Example:
| Period | Fiscal Year | Sales |
|---|---|---|
| 2014-03-01 - 2015-02-28 | 2014 | $200,000 |
| 2015-03-01 - 2016-02-28 | 2015 | $300,000 |
We can do this with the following 3 data wrangling steps.
- Extract Year and Month from the data column
- Create an adjustment column based on the Month
- Add (subtract) the adjustment to the Year .
1. Extract Year and Month from the data column
Year
Month
2. Create a new column to calculates an adjustment based on which month
We want to create an adjustment column which will have -1 for January and February and 0 for the other months so that we can subtract 1 from the year for January and February.
For this adjustment we can create a calculation with the ifelse function like below.
ifelse(order_date_month <= 2, - 1, 0)If a given month is Jan (1) or Feb (2) then it returns -1 otherwise 0.
Select ‘Mutate (Create Calculation)’ from the column header menu.
And type the above calculation.
3. Finally, we can add (subtract) the adjustment to the year column values.
Select ‘Mutate (Create Calculation)’ from the column header menu of the year column.
Add the ‘adjustment’ to the ‘order_date_year’ column.
Once we run this, we can see a new column ‘year_adjusted’ being created with the adjusted years. We can see that only the January and February have their year adjusted.
We can confirm the adjustments by using the Pivot Table like below.
