DAX Formula for Summing Sales on the Last Day of the Previous Month

DAX Formula

The following DAX formula computes the total sales for the last day of the previous month:

Sales_Last_Day_Previous_Month = 
VAR LastDayPrevMonth = EOMONTH(TODAY(), -1)
RETURN
    CALCULATE(
        SUM(Sales[SalesAmount]),
        Sales[OrderDate] = LastDayPrevMonth
    )

Explanation of the Formula

1. Variable Creation (VAR):

   • The variable LastDayPrevMonth is defined using the EOMONTH function. It computes the end of the month for the previous month relative to today's date (TODAY()).
   • EOMONTH(TODAY(), -1) returns the last date of the month before the current month.

2. CALCULATE Function:

   • The CALCULATE function modifies the filter context of the calculation, allowing for dynamic aggregation.
   • Within CALCULATE, the SUM(Sales[SalesAmount]) computes the total sales amount from the Sales table.

3. Filter Condition:

   • The filter condition Sales[OrderDate] = LastDayPrevMonth restricts the calculation to only consider sales from the last day of the previous month.

Practical Example

Assuming the following data in the Sales table:

• On today's date, let's say it is 2023-10-05. The last day of the previous month is 2023-09-30.
• The formula will sum SalesAmount where OrderDate is 2023-09-30.
• The result will be 100.

This DAX formula is particularly useful for financial reporting and analysis tasks where last month's performance metrics are needed, enabling efficient tracking of historical sales performance.