Forum Discussion
Anon2020
1 year agoHelper I
Dynamic Column Difference in Matrix
I have a matrix set up with Total sales by FY by Producyt Code. I am unable to find a formula that accurately depicts the difference betyween the two selected years that the user chooses from a filte...
- 1 year ago
Hi Anon2020
It seems to me that you're trying to show the min and max selected years and a column for the difference, if so try this DAX measure
Min Max Difference = VAR _minYr = MINX ( ALLSELECTED ( CalendarTable ), CalendarTable[Year] ) VAR _maxYr = MAXX ( ALLSELECTED ( CalendarTable ), CalendarTable[Year] ) VAR _years = { _minYr, _maxYr } VAR _minYrValue = SUMX ( FILTER ( ALLSELECTED ( CalendarTable[Year] ), CalendarTable[Year] = _minYr ), [Total Sales] ) VAR _maxYrValue = SUMX ( FILTER ( ALLSELECTED ( CalendarTable[Year] ), CalendarTable[Year] = _maxYr ), [Total Sales] ) RETURN SWITCH ( TRUE (), NOT ( HASONEVALUE ( CalendarTable[Year] ) ), _maxYrValue - _minYrValue, SELECTEDVALUE ( CalendarTable[Year] ) IN _years && HASONEVALUE ( CalendarTable[Year] ), [Total Sales] )Please see the attached pbix.