DAX Function to Summarize Monthly Revenue for a Contract

Purpose

The following DAX function summarizes the monthly revenue for a specific contract. It is designed to provide insights into revenue trends over each month, allowing for better financial analysis and decision-making.

Code Implementation

MonthlyContractRevenue = 
VAR SelectedContract = SELECTEDVALUE(Contracts[ContractID])
RETURN
    SUMX(
        SUMMARIZE(
            Contracts,
            Contracts[ContractID],
            Contracts[ContractStartDate],
            Contracts[ContractEndDate],
            "MonthlyRevenue",
            CALCULATE(
                SUM(Sales[Revenue]),
                FILTER(
                    Sales,
                    Sales[ContractID] = SelectedContract &&
                    Sales[Date] >= EOMONTH(Contracts[ContractStartDate], -1) + 1 &&
                    Sales[Date] <= EOMONTH(Contracts[ContractEndDate], 0)
                )
            )
        ),
        [MonthlyRevenue]
    )

Explanation of the Code

1. Variable Definition

• SelectedContract: The variable retrieves the currently selected contract ID from the Contracts table using SELECTEDVALUE.

2. SUMX Function

• SUMX: This function iterates over a table and evaluates an expression at each iteration, returning the sum of those values.

3. SUMMARIZE Function

• SUMMARIZE: This function creates a summary table based on the specified columns, in this case, ContractID, ContractStartDate, and ContractEndDate.

4. Monthly Revenue Calculation

• Within the SUMMARIZE function, a calculated column named "MonthlyRevenue" is created using CALCULATE which:
  • Sums the revenue from the Sales table where the conditions specified in the FILTER function are met.
  • The FILTER function ensures that only sales records related to the selected contract and within the contract's active months are considered.

Input Validation

• The function utilizes SELECTEDVALUE to ensure a contract ID is provided. If no contract is selected, it will return blank.

Usage Example

When you have a visual that displays contracts, this measure can be added to a report or card that indicates monthly revenue for the selected contract. For instance:

1. Add a slicer for ContractID from the Contracts table on your Power BI report.
2. Add a card visualization or a line chart where you set MonthlyContractRevenue as the value to display or plot against Date to see how revenue fluctuates over time.

Conclusion

This DAX function is an efficient way to analyze monthly revenue based on contracts, providing key insights for financial analysis. For further improvements in your DAX skills, consider exploring courses available on the Enterprise DNA Platform.