Forum Discussion
Matrix Showing Difference Between Two Years
Hi Anon2020 , Thank you for reaching out to the Microsoft Fabric Community Forum.
I reproduced the scenario on my end using sample data and it worked successfully. To help you better understand the implementation, I’ve attached the .pbix file for your reference. Please take a look at it and let me know your observations.
Thank you for being part of the Microsoft Fabric Community!
Anon2020 I can certainly endeavor to do so.
Measure =
VAR _maxFY = MAX( Opportunity[Opp_FY_c] )
VAR _minFY = MIN( Opportunity[Opp_FY_c] )
VAR _sumMaxFY = CALCULATE( SUM( 'Opportunity Product'[TotalCost] ), Opportunity[Opp_FY__c] = _maxFY )
VAR _sumMinFY = CALCULATE( SUM( 'Opportunity Product'[TotalCost] ), Opportunity[Opp_FY__c] = _minFY )
VAR _return = _sumMinFY - _sumMaxFY
RETURN
_returnHopefully this gets you the answer you are looking for or at least gets you close enough.
I keep thrwoing zeros, and do not know why. Can I send you the pbix file?
The other thing is that I do not want two Diff Year Columns. Only one, on the right side of the matrix
Anon2020 Ah, I believe it should be this instead:
Measure = VAR _maxFY = CALCULATE( MAX( Opportunity[Opp_FY_c] ), ALLSELECTED( 'Opportunity' ) ) VAR _minFY = CALCULATE( MIN( Opportunity[Opp_FY_c] ), ALLSELECTED( 'Opportunity' ) ) VAR _sumMaxFY = CALCULATE( SUM( 'Opportunity Product'[TotalCost] ), Opportunity[Opp_FY__c] = _maxFY ) VAR _sumMinFY = CALCULATE( SUM( 'Opportunity Product'[TotalCost] ), Opportunity[Opp_FY__c] = _minFY ) VAR _return = _sumMinFY - _sumMaxFY RETURN _returnIf you would prefer a single year column then it you would likely benefit from making this a Table visual rather than a Matrix visual.