Calculating the Last Day of each Month

Preview

You may need to extract the data of the last day of each month, such as the last day of the month in which the Payment Time falls, as shown in the following figure.

Note:

The example in this document does not involve operations on the hour-minute-second data. To extract the year-month-day data, you can use the LEFT function to extract the first 10 characters of each field.

Implementation Method

Use the DATE function in combination with YEAR, MONTH, and other functions to obtain the last day of each month.

Sample data: Contract Payment Facts Table.xlsx

Creating an Analysis Subject

Create an analysis subject and upload the sample data.

Calculating the Last Day of Each Month

Click Formula Column and name the added column as Last Day of Each Month. Enter the formula DATE(YEAR(Payment Time),MONTH(Payment Time)+1,1)-1 and click OK.

Note:

You can select the fields you need from the left area or enter the first few letters and select the one you need in the pop-up list. Fields in black entered manually do not take effect.

Formula explanation:

After you calculate the last day of each month, you can click Save All and Update or perform further calculations.

Demonstration

For the demonstration of this KPI card, see the second figure in section "Preview".

To calculate the last day of the current month, you can use the TODAY function and the DAYSOFMONTH function with the formula DATE(YEAR(TODAY()),MONTH(TODAY()),DAYSOFMONTH(TODAY())).

Formula explanation: