How to calculate the period of the previous month.

There are lots of ways to solve this; I will provide a few and you can choose and modify however you see fit.

I am making the assumption you want to get the data for a given month; most likely the previous month, but you could easily get a different month.

1. Variables in WEBI

First, I created a variable so I could test various dates (e.g. crossing years, different length months)

Var Reference Date=ToDate("08/30/2021"; "M/d/yyyy")

Next we want to create a variable to hold the first day of the month previous to Var Reference Date. We do that by subtracting a month, extracting the month number, concatenating that together with “/1/” and the year number from the previous, and finally converting it to an actual date data type.

Var First Day of Last Month
=ToDate(FormatNumber(MonthNumberOfYear(RelativeDate([Var Reference Date];-1;MonthPeriod)); "#0") + 
"/1/" + 
FormatNumber(Year(RelativeDate([Var Reference Date];-1;MonthPeriod)); "0000"); "M/d/yyyy")

Now we can get the end of the month previous to Var Reference Date by adding a month and then subtracting a day.

Var Last Day of Previous Month
=RelativeDate(RelativeDate([Var First Day of Previous Month]; 1; MonthPeriod); -1; DayPeriod)

I can plug any date into Var Reference Date and get the first and last days of the previous month.

2. Build a Universe

A number of years ago I built a Calendar based universe to do this very thing. I described it in this First day of previous week with a twist blog post. I can easily get the first and last days of the previous month and many other dynamic date values with this approach.

3. Free-hand SQL

The Calendar universe mentioned above it just a lot SQL based on a Calendar table with about 75 years’ worth of dates. So if you have a Calendar table available to you, but you do not want to build a universe you could create a query based on free-hand SQL something like this…

SELECT
      Calendar.dt                                            AS [Reference Date]
    , DATEADD(MONTH, DATEDIFF(MONTH, 0, Calendar.dt) - 1, 0) AS [First Day of Previous Month]
    , DATEADD(MONTH, DATEDIFF(MONTH, 0, Calendar.dt), 0) - 1 AS [Last Day of Previous Month]
FROM  Calendar
WHERE Calendar.dt = '08/30/2021 00:0:0'

This is SQL Server syntax. If you have a different database, you will need to modify this query to work with your database. If you do not have a Calendar table, but would like one there are a lot of resources on-line on how to create one. If you do not have a Calendar table and do not want to create one you can use a free-hand SQL statement like this…

DECLARE @ReferenceDate DATE;
SET @ReferenceDate = '08/30/2021 00:0:0';
SELECT
    @ReferenceDate                                            AS [Reference Date]
  , DATEADD(MONTH, DATEDIFF(MONTH, 0, @ReferenceDate) - 1, 0) AS [First Day of Previous Month]
  , DATEADD(MONTH, DATEDIFF(MONTH, 0, @ReferenceDate), 0) - 1 AS [Last Day of Previous Month]

Important Notice for college students

If you’re a college student and have skills in programming languages, Want to earn through blogging? Mail us at geekycomail@gmail.com

For more Programming related blogs Visit Us Geekycodes . Follow us on Instagram.