Calculating sales

To start with a straightforward measure, let us calculate the total sales amount:

Total Sales = 
CALCULATE(
    SUM(fProjectSales[Budget]),
    USERELATIONSHIP(fProjectSales[StartDate], 'Calendar'[Date])
)

As USERELATIONSHIP activates the relationship between the fProjectSales table and the Calendar table on the StartDate column, this measure returns the amount sold in each month. While this is valuable information in terms of order intake, when working with projects that may span multiple years, another valuable insight would be to have the project budget spread out over the duration of the project.

There are several ways to do this. The easiest would be to divide the project budget by the duration of the project, and take that amount for each month that the project will run. This assumes that the income from the project will be evenly spread over the months.

While the duration of a project could be derived from the fFTE table, a more convenient approach would be to have a duration column in the ProjectType table; after all, the duration is a fixed attribute of a project type. As the fFTE table starts counting months from zero, let us just take the highest month number, which is then one less than the actual duration of the project type. To avoid confusion, we will name this column MaxMonth:

MaxMonth = CALCULATE(MAX(fFTE[Month]))

Note that we have to use CALCULATE here to make sure we get the largest Month value for the corresponding project types, not for all project types.

Let us assume that all calculations can be done on a month-by-month basis. The first month of the project, which is the month in which the start date of the project falls, is month zero; the last month is month zero plus MaxMonth.

For the calculations in this chapter, it is important to understand the consequences of this assumption. As an example, consider a project with February 15, 2022, as the start date, and a MaxMonth value of 3. This MaxMonth value means that we have four months of fFTE data, for February, March, April, and May, 2022. The project budget will be spread over these four months. You could be tempted to think that the duration of this project is four months, meaning that the project would run until June 15; however, the month of June does not count for our calculations.

This means that, given a selection on the Calendar, a project is active during (part of) that time period when:

• The first month of the project starts before the last date in the Calendar selection;
• And the project's last month ends after the first date in the selection.

The figure below illustrates this schematically for a few projects with MaxMonth = 2.

Figure 2.8.3: Active projects

Our first challenge is to determine the projects that are active during the selection on the Calendar table. The DAX formula below starts by filtering the fProjectSales table, implementing the logic outlined above:

Sales (over time) =
VAR MaxDate = MAX('Calendar'[Date])
VAR MinDate = MIN('Calendar'[Date])
RETURN
SUMX(
    FILTER(
        fProjectSales,
        EOMONTH(fProjectSales[StartDate], -1) + 1
        <= MaxDate
        && EOMONTH(
            fProjectSales[StartDate], RELATED(ProjectType[MaxMonth]
        ) >= MinDate
    ),

Let us pause here for a while and see what is happening. After storing the first and last days of the Calendar selection in variables, we traverse the fProjectSales table and apply the logic to retrieve the projects active within the selected period. The EOMONTH function adds a number of months to a date value, in this case fProjectSales[StartDate], then moves that date to the end of the month. The first EOMONTH expression returns the last day of the month preceding the start date, and adding 1 day gives us the first day of month zero. Adding MaxMonth in the second EOMONTH expression brings us to the last day of the last month of the project.

Now that we have selected the right projects, we can continue calculating the desired results:

    VAR ProjectBudget = fProjectSales[Budget]
    VAR ProjectStartDate = fProjectSales[StartDate]
    VAR ProjectMaxMonth = RELATED(ProjectType[MaxMonth])
    VAR MonthlyBudget = 
        DIVIDE(
            ProjectBudget, 
            ProjectMaxMonth + 1
        )
    VAR ActiveMonths =
    CALCULATETABLE(
        DISTINCT('Calendar'[YearMonthCtr]),
        KEEPFILTERS(
            'Calendar'[Date] >= ProjectStartDate
            && 'Calendar'[Date] <= 
            EOMONTH(ProjectStartDate, ProjectMaxMonth)
        )
    )
    RETURN
    COUNTROWS(ActiveMonths) * MonthlyBudget
)

After declaring the variables containing the values we need for the calculation, the ActiveMonths variable is the list of months (or YearMonthCtr values – this being a continuously increasing counter at the month level) in the Calendar selection that are within the duration of the project: between the start date and the end date. Note the use of KEEPFILTERS here to avoid losing any filters on the Calendar table; in this way, we get the overlap between the Calendar selection and the project duration.

The end result by project is the number of active months multiplied by the monthly project budget, which is the part of the total project budget expected to be spent during the selected period.

The figure below shows the difference between the Total Sales and Sales (over time) measures, for two sample projects:

Figure 2.8.4: Total Sales and Sales (over time)

In the chart, one project can be seen that starts in January 2021. The last month of this project is April 2022, and an evenly distributed amount is reported in every month between. A second project starts in February 2021, with a smaller total budget. The duration of the project is shorter, though; the budget is distributed over 5 months, indicating a MaxMonth value of 4.

Optimizing the sales calculation

The flaw in the Sales (over time) measure is, as you may have spotted already, that we iterate over a fact table. While you may assume that the number of multi-million projects sold is not super high, it is still worthwhile to see if we can change that.

The clear indicator here is the use of RELATED to retrieve information from a filter table in the process. Whenever your table aggregation contains the RELATED function, you should think about whether you can iterate over the filter table instead. In our case, the question is whether we can do the same calculation while iterating over the ProjectType table, and more specifically, the (unique) values of ProjectType[MaxMonth].

If we were to iterate over VALUES(ProjectType[MaxMonth]), we could calculate the total budget for all projects with that duration at once. Unfortunately, that does not help us completely: the calculation of the overlap between selected months and project duration months is not possible with only the duration.

For this alternative approach to work, we would also need to take the Calendar selection into account. Instead of doing the calculation for all months in the selection at once, we can go through the selection month by month. So, we iterate over the combinations of MaxMonth and YearMonthCtr values that are found in the query context. The table with combinations can be created using the CROSSJOIN function:

Sales (over time, optimized) =
VAR MonthDurationCombinations =
    CROSSJOIN(
        DISTINCT(ProjectType[MaxMonth]),
        DISTINCT('Calendar'[YearMonthCtr])
    )
RETURN
SUMX(
    MonthDurationCombinations,
    VAR ThisMaxMonth = ProjectType[MaxMonth]
    VAR ThisYearMonthCtr = 'Calendar'[YearMonthCtr]
    VAR MaxDate = 
    CALCULATE(
        MAX('Calendar'[Date]),
        ALL('Calendar'),
        'Calendar'[YearMonthCtr] = ThisYearMonthCtr
    )
    VAR MinDate =
    CALCULATE(
        MIN('Calendar'[Date]),
        ALL('Calendar'),
        'Calendar'[YearMonthCtr] = ThisYearMonthCtr – ThisMaxMonth
    )
    VAR TotalBudget = 
    CALCULATE(
        SUM(fProjectSales[Budget]),
        fProjectSales[StartDate] <= MaxDate
        && fProjectSales[StartDate] >= MinDate
    )
    RETURN
    DIVIDE(TotalBudget, ThisMaxMonth + 1)
)

For each combination in the MonthDurationCombinations table, we first store the values in the ThisMaxMonth and ThisYearMonthCtr variables. Next, we need to select the projects active in this particular month. We can do that using their start date, for which we have to derive the earliest, MinDate, and latest, MaxDate, possible start dates for a project to be active. The latest possible start date is, of course, the last day in this month; any project starting later is not yet active in this month. As for the earliest start dates, we can subtract ThisMaxMonth from the YearMonthCtr to find the earliest month possible. Any project starting earlier will have finished before this month starts.

To illustrate this, the example in the figure below shows, for month 66 and MaxMonth 2, that the earliest possible start date of an active project is the first day of month 64. Any day earlier, like the upper project, and the month zero of the project is month 63 and there is no budget left for month 66.

Figure 2.8.5: Finding the earliest possible start date

With this, we can compute the total budget of all active projects at once and, as the calculation is done by MaxMonth value, it is easy to divide this by the current duration and retrieve the budget projected for the current month.

The calculations involving MaxMonth are the reason why we use the month counter here, and not simply a month number: with the counter, we don't have to worry about year boundaries.

The number of iterations that SUMX has to do is equal to the number of different durations multiplied by the number of months in the query context. This will typically be only a small number and may be far better than traversing through all projects.