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 email@example.com