EOMONTH

How to Use the Power BI DAX function EOMONTH

In this article, we will explore how to use the EOMONTH function in Power BI.

What is the EOMONTH Function?

The EOMONTH function is a DAX function that returns the last day of the month before or after a specified number of months. The function takes two arguments: a date and a number of months.

The syntax of the EOMONTH function is as follows:

EOMONTH(❰date❱, ❰months❱)

Where:

- `❰date❱` is the date for which you want to calculate the last day of the month.

- `❰months❱` is the number of months before or after the `❰date❱` for which you want to calculate the last day of the month.

How to Use the EOMONTH Function

To use the EOMONTH function in Power BI, follow these steps:

1. Open Power BI and create a new report or open an existing report.

2. Add a table or a visual that contains a date column that you want to calculate the last day of the month for.

3. Click on the table or visual to select it.

4. Click on the "New Measure" button in the "Modeling" tab of the ribbon.

5. In the "New Measure" dialog box, enter a name for the measure and enter the following formula:

LastDayOfMonth = EOMONTH(❰date column❱, 0)

Replace `❰date column❱` with the name of the date column in your table or visual.

6. Click "OK" to create the measure.

The LastDayOfMonth measure will now be available for use in your report. You can use this measure in any visual or table to calculate the last day of the month for the date column.

Example of Using the EOMONTH Function

To better understand how to use the EOMONTH function, consider the following example.

Suppose we have a table that contains sales data for a company. The table has a date column called "Date" and a sales column called "Sales". We want to create a visual that shows the total sales for the last day of each month.

To do this, we can create a measure using the EOMONTH function as follows:

1. Click on the table or visual that contains the date column.

2. Click on the "New Measure" button in the "Modeling" tab of the ribbon.

3. Enter a name for the measure, such as "LastDaySales", and enter the following formula:

LastDaySales = CALCULATE(

SUM(Sales),

FILTER(

‘Table’,

Date = EOMONTH(Date, 0)

)

)

Replace `'Table'` with the name of your table, and replace `'Sales'` and `'Date'` with the names of your sales column and date column, respectively.

4. Click "OK" to create the measure.

You can now add a visual to your report, such as a column chart or a table, and add the "LastDaySales" measure to the visual. The visual will now show the total sales for the last day of each month.

The EOMONTH function is a useful tool in Power BI that allows users to calculate the last day of a given month. This function is particularly useful when working with time-based data, as it allows users to aggregate data by month and perform various calculations on the data. By following the steps outlined in this article, you can easily use the EOMONTH function in your Power BI reports to calculate the last day of the month for a given date column.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)