DAX Formula for Month-to-Date (MTD)

1. Objective

To create a DAX measure that calculates the Month-to-Date (MTD) value for a given metric, such as sales.

2. DAX Formula

The DAX formula for calculating MTD is as follows:

MTD_Sales = TOTALMTD(SUM(Sales[SalesAmount]), Sales[Date])

Explanation of the Formula Components

• MTD_Sales: This is the name of the measure that will output the Month-to-Date sales.
• TOTALMTD: This is the DAX function that calculates the total for a measure from the beginning of the month to the current date in the context of the report.
• SUM(Sales[SalesAmount]): This part sums the values of the SalesAmount column from the Sales table, which contains individual sales transactions. This defines the metric to aggregate for MTD calculation.
• Sales[Date]: This is the date column from the Sales table that the MTD calculation uses to determine the current date context.

3. Practical Example

Imagine you have a Sales table structured as follows:

MTD Calculation Scenario:

• If the current date in the report context is 2023-10-10, the MTD_Sales measure will calculate the total sales from the beginning of October (2023-10-01) to 2023-10-10.

Calculation:

• MTD_Sales will sum up the SalesAmount for records with Date <= 2023-10-10:
  • Sale 1: 100 (2023-10-01)
  • Sale 2: 200 (2023-10-05)
  • Sale 3: 0 (2023-10-12 is after the context date)

Thus, the result for MTD_Sales would be 300.

4. Additional Considerations

• Ensure that your data model has a date table connected to the sales data for improved filtering and time intelligence calculations.
• The TOTALMTD function automatically adjusts based on the current report filter context, making it ideal for dashboards and reports that display monthly performance metrics.

5. Conclusion

The example provided demonstrates how to create a Month-to-Date measure in DAX, which is essential for evaluating performance metrics over current months effectively. For further DAX training and advanced analytical techniques, consider exploring courses on the Enterprise DNA Platform.