Built-in Functions → monthEndDate
monthEndDate() is a scalar date function that returns the date of the last day of the current month.
Signature
monthEndDate()
Returns
Date representing the last date of the current month.
Example
Consider a case where you want to identify the end of the current month and the sales revenue that occurred before the last date of the current month, for instance, 6/30/2021.
- End date of the current month
monthEndDate()
- Sales prior to month end
SALES.SALES.TIME_ID < monthEndDate()
The following table illustrates the behavior of the monthEndDate() date function:
| Product | Revenues | Sales Date | monthEndDate() | Sales prior to monthEndDate() |
|---|---|---|---|---|
| CD | $200 | 6/11/2021 | 6/30/2021 | true |
| Hard disk | $600 | 6/12/2021 | 6/30/2021 | true |
| Cable | $550 | 6/14/2021 | 6/30/2021 | true |
| Hardware | $800 | 6/21/2021 | 6/30/2021 | true |
| Memory Card | $220 | 7/1/2021 | 6/30/2021 | false |
Use the following steps for detailed instructions on how to use the monthEndDate() date function:
Note
In the Cluster Management Console (CMC), create a tenant that includes Sample Data. The Sample Data includes the SALES schema.
- In the Navigation bar, select the Content tab, and then select + New → Add Dashboard.
- In the Add Dashboard dialog, for Name, enter Product Dashboard, and then select Add.
- In the Action bar, select + (add icon), or select + Add Insight.
- In the Insight panel, select Listing Table or V.
- In Tables, select Aggregated Table.
- In the Data panel, select Add Data Set (+).
- In the Manage Data Sets panel, in Tables, select SALES. Close the panel.
- From the Data panel, drag and drop the following columns to the respective tray:
- From the Products table, drag and drop Category to the Grouping Dimension tray.
- From the Sales table, drag and drop Revenue to the Measure tray. In the Properties panel,
- For Number Format, select Dollar Rounded.
- From the Data panel, drag and drop Add Formula to the Grouping Dimension tray.
- The Formula Builder automatically opens:
- In Search Functions, select the down arrow, and then select Date Functions.
- Double-click the monthEndDate function,
monthEndDate(), to add the formula to the editor. - Select Validate & Save.
- In the Properties panel, for Column Label, enter monthEndDate().
- From the Data panel, drag and drop Add Formula to the Grouping Dimension tray.
- In the Formula Editor, use the following condition:
SALES.SALES.TIME_ID < monthEndDate()
- Select Validate & Save.
- In the Properties panel, for Column Label, enter Sales prior to monthEndDate().
- In the Formula Editor, use the following condition:
- Name the insight Revenue Per Product Category.
- In the Action bar, select Save.
