Calculate Previous Month Turnover in DAX

This document provides a DAX function to calculate the sum of turnover from the previous month. It includes imports, documentation, input validation, inline comments, and a usage example.

Function Overview

The function PreviousMonthTurnover computes the total turnover from the previous month based on a given date column in the specified table.

DAX Code

PreviousMonthTurnover = 
VAR CurrentMonth =
    EOMONTH(TODAY(), -1)    -- Get the last day of the previous month
VAR StartDate =
    EOMONTH(CurrentMonth, -1) + 1  -- Get the first day of the previous month
VAR EndDate =
    CurrentMonth               -- Last day of the previous month

RETURN
    CALCULATE(
        SUM(Sales[Turnover]),    -- Sum of turnover
        Sales[TransactionDate] >= StartDate && Sales[TransactionDate] <= EndDate  -- Filter for previous month
    )

Code Explanation

1. Variables:

   • CurrentMonth computes the last day of the previous month using EOMONTH.
   • StartDate is the first day of the previous month.
   • EndDate refers to the last day of the previous month.

2. RETURN Clause:

   • The CALCULATE function is utilized to filter the sales data based on the date range from StartDate to EndDate.
   • It sums the Turnover values from the Sales table.

Input Validation

In DAX, input validation is primarily handled by ensuring the data structure is correct. Make sure the Sales table exists with a Turnover column and a TransactionDate column of Date data type before using this function.

Example Usage

In a Power BI report, you can create a measure using the above DAX code. Once added, use it in visuals or reports to analyze performance trends.

Assuming you have a Sales table structured with columns for Turnover and TransactionDate, placing this measure on a report will reflect the turnover from the previous month.

Conclusion

This approach effectively calculates the sum of turnover from the previous month. It's efficient and leverages DAX best practices. For further learning on DAX and advanced analytical techniques, consider exploring courses offered by the Enterprise DNA Platform.