Calculate Previous Quarter Start and End Date
Hello!
I am looking to calculate the Previous Quarter Start/End Date and the Previous Two Quarter Start/End Date.
For example:
Input:
November 8, 2022
Desired Output:
Previous Quarter Start Date - July 1, 2022
Previous Quarter End Date - September 30, 2022
Previous Two Quarter Start Date - April 1, 2022
Previous Two Quarter End Date - June 30, 2022
Work In Progress:
Answers
-
These are untested but something like this should get you what you're looking for:
Previous Quarter Start Date
LAST_DAY(`Report Date`) - INTERVAL (MOD(MONTH(`Report Date`), 3) + 3) MONTH + INTERVAL 1 DAY
Previous Quarter End Date
LAST_DAY(`Report Date`) - INTERVAL (MOD(MONTH(`Report Date`), 3)) MONTH
Previous Two Quarter Start Date
LAST_DAY(`Report Date`) - INTERVAL (MOD(MONTH(`Report Date`), 3) + 6) MONTH + INTERVAL 1 DAY
Previous Two Quarter End Date
LAST_DAY(`Report Date`) - INTERVAL (MOD(MONTH(`Report Date`), 3) + 3) MONTH
LAST_DAY(`Report Date`)
Returns the date of the last day in the month which you're aware of but clarifying it for others.
MOD(MONTH(`Report Date`), 3)
This tells us how many months to subtract to get to the prior quarter based on the Report Date's quarter. It's dividing the month number by three to get the remainder. For November it'll be 2. 11-2 = 9 (September)
- INTERVAL ... MONTH
Subtract the specific number of months calculated above. Adding 3 to that number will take us back another entire quarter or 6 to go back two more quarters.
+ INTERVAL 1 DAY
Add a day from the last day in the quarter 2 (+3 months) or 3 (+6 months) to get to the first day of the quarter 1 or 2 quarters ago.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Thank you for the response! I noticed however that I still receive the incorrect response. The function calculates the date from the previous quarter (Example, input is September 15, output is August 15, rather than expected output to be the first or the last day of the month of the quarter, July 1 or August 31)
Workaround that I found is to build logic to determine the previous quarter, and then base on that information set the start and end dates of each quarter as they are constant.
0 -
@AddieB Can you share how you what format you used for the "previous quarter", and what formula you used to take the first date of that "previous quarter".
Thank you!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive