How do I keep a column in the filter context and perform a sumx calculation?

Question

I'm trying to return the sumproduct of the next 4 years of a measure by a dynamic multiplier for different financial forecasts but I can't seem to get it to work. Wondered if anyone could help?

My data is in a flat table:

ForecastName    FiscalYear  TransactionType Value
Forecast1      2019         borrowing       10
Forecast1      2020         borrowing       10
Forecast1      2021         borrowing       10
Forecast1      2022         borrowing       20
Forecast1      2023         borrowing       25
Forecast2      2019         borrowing       30
Forecast2      2019         turnover        40
Forecast3      2019         turnover        50
Forecast3      2020         turnover        50

I have a measure to calculate borrowing:

Borrowing = CALCULATE(SUM(Table[Value]),Table[TransactionType]="borrowing")

And a measure for the mutiplier (basically it returns {1, 1, 0.5, 0}):

Multiplier = 
VAR YearNumber = MAX(Table[Fiscal Year])-2018
RETURN
IF(2.5 > YearNumber,1, IF(2.5 > YearNumber-1,.5,0))

Here's my attempt to write the sum product style measure:

Borrowing Forecast = 
VAR CurrentFiscalYear = MAX(Table[Fiscal Year])

RETURN
SUMX((FILTER(ALLEXCEPT(Table,Table[ForecastName]),
           Table[Fiscal Year] < CurrentFiscalYear + 4 &&
           Table[Fiscal Year] >= CurrentFiscalYear)
Mutiplier x Borrowing)

The idea being that when you put the measure on a table with FiscalYear on the row, filtered by ForecastName, it grabs the Fiscal Year from the row and puts it in the variable, then uses this to filter the whole table on the current and next 3 Fiscal Years but keeps the filter on ForecastName, then performs the calculation and sums it. The table shows my desired result for Forecast1:

Fiscal Year Borrowing Forecast - DESIRED
2019         25 ((10*1)+(10*1)+(10*0.5)+(20*0))
2020         30 ((10*1)+(10*1)+(20*0.5)+(25*0))

The actual result is a big number I don't understand but I think one of the problems is the ALLEXCEPT part doesn't seem to be working..

I also wondered if the fact that the measure I'm trying to use in the calculation part of SUMX contains CALCULATE?

Many thanks!

Alexis Olson Alexis Olson · Accepted Answer · 2020-04-02T19:25:08

This is tricky. It's not too bad if you're willing to define the multiplier within your iterator but I'm still trying to figure out how to abstract that out while passing both row context properly.

Here's how I might do it as a single measure:

Borrowing Forecast = 
VAR ThisYear = SELECTEDVALUE ( 'Table'[FiscalYear] ) 
VAR Years =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        'Table'[FiscalYear],
        "Multiplier", SWITCH (
            TRUE (),
            'Table'[FiscalYear] < ThisYear, 0,
            'Table'[FiscalYear] - ThisYear < 2, 1,
            'Table'[FiscalYear] - ThisYear < 3, 0.5,
            0
        ),
        "Borrowing", CALCULATE (
            SUM ( 'Table'[Value] ),
            'Table'[TransactionType] = "borrowing"
        )
    )
RETURN
    SUMX ( Years, [Multiplier] * [Borrowing] )

The Borrowing measure is easy to define separately but the multiplier is trickier...

OK. I think I've got it with some DAX hacking magic.

To factor out the multiplier, let's define it like this where

MinYear = CALCULATE ( MIN ( 'Table'[FiscalYear] ), ALL ( 'Table' ) )

Multiplier = 
VAR YearDiff = SELECTEDVALUE ( 'Table'[FiscalYear] ) - [MinYear]
RETURN
    SWITCH (
        TRUE (),
        YearDiff < 0, 0,
        YearDiff < 2, 1,
        YearDiff < 3, 0.5,
        0
    )

Now we can write the forecast measure as follows:

Forecast = 
VAR Offset = SELECTEDVALUE ( 'Table'[FiscalYear] ) - [MinYear]
RETURN
    SUMX (
        ALL ( 'Table'[FiscalYear] ),
        CALCULATE (
            [Multiplier],
            'Table'[FiscalYear] = EARLIER ( 'Table'[FiscalYear] ) - Offset
        ) * [Borrowing]
    )

Note that we are essentially passing EARLIER ( 'Table'[FiscalYear] ) - Offset as a variable to the Multiplier measure. Also note that the Offset contains [MinYear] which cancels out with the one in YearDiff after getting passed through so that effectively

YearDiff = EARLIER ( 'Table'[FiscalYear] ) - SELECTEDVALUE ( 'Table'[FiscalYear] )

where the former is the year from row context and the latter is the year from filter context.

The [MinYear] and Offset are there because you can't pass an arbitrary variable but only those that exist in the table so things need to be shifted around to keep it within the existing range of years.

Here's what it looks like in a matrix:

How do I keep a column in the filter context and perform a sumx calculation?

1 Answers