Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Did I miss something? I would appreciate if an expert would correct me if I understood something wrong. Thanks!
Solved! Go to 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.
[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.
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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
22 | |
20 | |
15 | |
13 |
User | Count |
---|---|
49 | |
41 | |
39 | |
19 | |
19 |