Understand filter context with FILTER, ALL and MIN (compute a previous Value (no Date column!))

[Anonymous]

I've a fact table with a column cycle, which refer to the cycle of the collected quantity.

I would like to compare the total quantity of a cycle to the previous cycle. Here it's how the result look likes.

Following measure is first defined

qty := SUM ( yield[quantity] )

I first tried without success, to compute with the previous cycle number

previous cycle :=
VAR Cycle = VALUES(yield[cycle])

RETURN 
IF(COUNTROWS(Cycle) = 1;
    Cycle - 1
)

using the above measue with this one I got no number at all (last column on pivot, s. screenshot above)

quantity previous cycle wrong :=
CALCULATE ( 
    [qty]; 
    FILTER (
        ALL ( yield[cycle] );
        yield[cycle] = [previous cycle]
    )
)

I presume, that the filter context on the cycle column is not removed so.

after struggling quite a bit, I found this solution

quantity previous cycle :=
CALCULATE ( 
    [qty]; 
    FILTER (
        ALL ( yield[cycle] );
        MIN(yield[cycle]) - 1 = yield[cycle]
    )
)

With this last measure, I get exactly what I need, however I don't understand completely WHY it works.

So is my current understanding:

• ALL remove the filter context on the cycle column
• MIN()  (btw, MAX() would work as well) has for me some magic there.
  • it takes the minimum from the row context (in Pivot table, there is one single value, e.g. 4) -> scalar value
  • substract 1 of it (=3)
  • and filter through all Value of the filter context, the only single cycle I want to compare to

Did I miss something? I would appreciate if an expert would correct me if I understood something wrong. Thanks!

[Anonymous · Accepted solution]

[quantity previous cycle] :=
// SELECTEDVALUE returns the value as visible in the
// current context and blank if more than 1 value
// is visible.
var __currentCycle = SELECTEDVALUE( yield[cycle] )
// This code works because if __currentCycle is blank
// then __previousCycle is -1 and such a cycle
// does not exist, so blank will be returned
// as expected.
var __previousCycle = __currentCycle - 1
RETURN
// Please bear in mind that any filter created
// in CALCULATE OVERWRITES any filter on the
// same column that comes from outside. If you want
// to put the filters on the same column in an AND
// condition you have to use KEEPFILTERS.
	CALCULATE (
	    [qty],
	    // Filter returns the values in the cycle
	    // column that are equal to __previousCycle.
	    FILTER (
	    	// ALL returns all distinct values
	    	// in the cycle column disregarding
	    	// any current filters.
	        ALL ( yield[cycle] ),
	        // This filters all the cycle's
	        // for one cycle - the previous one.
	        yield[cycle] = __previousCycle
	    )
	)
	
// The above is fully equivalent to the following:

[quantity previous cycle] :=
// SELECTEDVALUE returns the value as visible in the
// current context and blank if more than 1 value
// is visible.
var __currentCycle = SELECTEDVALUE( yield[cycle] )
// This code works because if __currentCycle is blank
// then __previousCycle is -1 and such a cycle
// does not exist, so blank will be returned
// as expected.
var __previousCycle = __currentCycle - 1
RETURN
	CALCULATE (
	    [qty],
	    yield[cycle] = __previousCycle
	)

Best

D.

[Anonymous]

Thank you for the excellent alternative with SELECTEDVALUE and the use of variables. I didn't know this function resp. I'm still not so familiar with using variables.

Here for the sake of completeness your last version, who works with Power Pivot / Excel 2016 as well (SELECTEDVALUE not available):

[quantity previous cycle] :=
var __currentCycle = IF ( HASONEVALUE( yield[cycle] ), VALUES ( yield[cycle] ) )
var __previousCycle = __currentCycle - 1
RETURN
	CALCULATE (
	    [qty],
	    yield[cycle] = __previousCycle
	)