Monthly Revenue Forecast DAX Function

Monthly Revenue Forecast DAX Function

Overview

In this task, we will create a DAX function to forecast the monthly revenue from business contracts that are closed annually and invoiced annually. Each contract’s annual value will be converted to daily revenue, then summed to monthly and yearly totals.

Detailed Steps

1. Calculate the daily revenue from the annual contract value.
2. Distribute the daily revenue across each month of the contract period.
3. Sum the daily revenues to get monthly and yearly totals.

DAX Function

Necessary Imports

Ensure your data model includes at least Contracts table and a Date table.

Function Definition

-- Define the MonthlyRevenueForecast function
MonthlyRevenueForecast = 
VAR ContractsFiltered = 
    FILTER(
        Contracts, 
        Contracts[ContractValue] > 0 && YEAR(Contracts[StartDate]) > 2019
    ) -- Assume we are filtering contracts from the year 2020 onwards
    
VAR DailyRevenues = 
    ADDCOLUMNS(
        ContractsFiltered,
        "ContractDays", DATEDIFF(Contracts[StartDate], Contracts[EndDate], DAY),
        "DailyRevenue", DIVIDE(Contracts[ContractValue], DATEDIFF(Contracts[StartDate], Contracts[EndDate], DAY))
    ) -- Calculate daily revenue per contract
    
VAR RevenueTable = 
    GENERATE(
        DailyRevenues,
        VAR ContractStart = Contracts[StartDate]
        VAR ContractEnd = Contracts[EndDate]
        RETURN
        ADDCOLUMNS(
            CALENDAR(ContractStart, ContractEnd),
            "RevenueDate", [Date],
            "DailyRevenue", DailyRevenues[DailyRevenue]
        )
    ) -- Generate a table with daily revenues over contract period

VAR DailyRevenueSummarized =
    SUMMARIZE(
        RevenueTable,
        [RevenueDate],
        "TotalDailyRevenue", SUM(RevenueTable[DailyRevenue])
    ) -- Summarize total daily revenue per date

VAR MonthlyRevenueSummarized =
    ADDCOLUMNS(
        SUMMARIZE(
            DailyRevenueSummarized,
            YEAR([RevenueDate]), MONTH([RevenueDate]),
            "Year", YEAR([RevenueDate]),
            "Month", MONTH([RevenueDate])
        ),
        "MonthlyRevenue",
        CALCULATE(
            SUMX(
                FILTER(
                    DailyRevenueSummarized,
                    YEAR(DailyRevenueSummarized[RevenueDate]) = EARLIER(YEAR(DailyRevenueSummarized[RevenueDate])) &&
                    MONTH(DailyRevenueSummarized[RevenueDate]) = EARLIER(MONTH(DailyRevenueSummarized[RevenueDate]))
                ),
                DailyRevenueSummarized[TotalDailyRevenue]
            )
        )
    ) -- Summarize monthly revenue

RETURN
    SELECTCOLUMNS(
        MonthlyRevenueSummarized,
        "Year", MonthlyRevenueSummarized[Year],
        "Month", MonthlyRevenueSummarized[Month],
        "MonthlyRevenue", MonthlyRevenueSummarized[MonthlyRevenue]
    )

Explanation

• Step 1: Filter the contract data to include only those contracts with a positive value and starting from year 2020 onwards.
• Step 2: Calculate the daily revenue for each contract.
• Step 3: Generate a table extending the dates from start to end date of the contract and assign daily revenue to each day.
• Step 4: Summarize the daily revenues by date to get the total daily revenue.
• Step 5: Aggregates daily revenue into monthly totals by year and month.
• Step 6: Return a summarized table displaying year, month, and the calculated monthly revenue.

Usage Example

Once the function MonthlyRevenueForecast is added to your DAX model, you can use it to visualize the monthly revenue forecast:

EVALUATE MonthlyRevenueForecast 
ORDER BY [Year], [Month]

Summary

This structured approach not only distributes revenue efficiently but also allows for easy modifications if the contract or revenue details change. For further advanced DAX learning, consider attending courses from the Enterprise DNA Platform.

For further coding efficiency and structure, always validate the integrity of your data and periodically review DAX optimization techniques.