Take the last day of the month under the current date

1.1 Expected effect

Sometimes users need to get the data of the last day of each month. For example, it is necessary to calculate the last day of each "PurchaseDate", as shown in the following figure:

1.2 Implementation ideas

Use the DATE function in combination with YEAR, MONTH and other functions.

The example uses the "FRDemo_EquipmentDetail" under "FRDemo".

2.1 Create a self-service dataset

Create a new self-service dataset, click "selected field>FRDemo_EquipmentDetail", select some fields in the table, as shown in the following figure:

2.2 Calculate the last day of each month

Click "+" to add a New column, as shown in the figure below:

Name the new column "Take the last day of the month under the current date" and enter the formula DATE (YEAR (PurchaseDate), MONTH (PurchaseDate) +1, 1-1). Click "OK", as shown in the figure below:

Note: The functions and fields in the formula box need to be selected by clicking the selection area on the left, and cannot be entered manually.

Formula description:

You can continue the calculation or save the self-service dataset.

2.3 Effect view

See section 1.1 of this article for details.

3. Matters needing attention

If you need to calculate the last day of the month of "current time", you can combine  TODAY and DAYSOFMONTH functions. Input formula DATE(YEAR(TODAY()),MONTH(TODAY()),DAYSOFMONTH(TODAY())). As shown below:

Formula description: