DATESMTD

How to Use the Power BI DAX function DATESMTD

One of the most useful DAX functions in Power BI is the DATESMTD function. This function allows you to calculate the total value for a measure from the beginning of the current month up to the selected date.

Syntax of the DATESMTD Function

The syntax of the DATESMTD function is:


DATESMTD(❰date❱)


The ❰date❱ parameter is the date for which you want to calculate the total value of the measure. This parameter can be a column that contains dates or a date value.

How to Use the DATESMTD Function

To use the DATESMTD function, you need to follow these steps:

1. Open Power BI Desktop.

2. Create a new measure by clicking on the "New Measure" button in the "Modeling" tab.

3. In the formula bar, type the following formula:


Total Sales MTD = CALCULATE(SUM(Sales[SalesAmount]),DATESMTD(DimDate[DateKey]))


4. Replace "Total Sales MTD" with the name of your measure.

5. Replace "Sales[SalesAmount]" with the name of your sales column.

6. Replace "DimDate[DateKey]" with the name of your date column.

Once you have completed these steps, your measure will be ready to use. The DATESMTD function will calculate the total value of your measure from the beginning of the current month up to the selected date.

Examples of Using the DATESMTD Function

Here are some examples of how you can use the DATESMTD function in Power BI:

Example 1: Total Sales MTD

Suppose you have a sales table that contains the following columns: "SalesAmount" and "SalesDate". You want to create a measure that calculates the total sales for the current month up to the selected date.

To do this, you can use the following formula:


Total Sales MTD = CALCULATE(SUM(Sales[SalesAmount]),DATESMTD(Sales[SalesDate]))


Example 2: Total Units Sold MTD

Suppose you have a sales table that contains the following columns: "UnitsSold" and "SalesDate". You want to create a measure that calculates the total units sold for the current month up to the selected date.

To do this, you can use the following formula:


Total Units Sold MTD = CALCULATE(SUM(Sales[UnitsSold]),DATESMTD(Sales[SalesDate]))


Example 3: Total Revenue MTD by Product

Suppose you have a sales table that contains the following columns: "Product", "SalesAmount" and "SalesDate". You want to create a measure that calculates the total revenue for each product for the current month up to the selected date.

To do this, you can use the following formula:


Total Revenue MTD by Product = CALCULATE(SUM(Sales[SalesAmount]),DATESMTD(Sales[SalesDate]),Sales[Product])


The DATESMTD function is a powerful tool that allows you to calculate the total value of a measure from the beginning of the current month up to the selected date. By using this function in your Power BI reports, you can gain valuable insights into your data and make informed business decisions.

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)