How To Hide Variances For This Year

[zfemmer]

I have a few measures that I am displaying in a table. I would like to hide the variance and variance % column for this year (2021) as the data is just 0s:

My current measures are these:
Revenue:

M_Revenue_MixOfBusinessActual = SUM(MixOfBusinessActual[Revenue])

Revenue This Year:

M_Revenue_ThisYear_MixOfBusinessActual = 
VAR _AccountingMonthSort = CALCULATE(MAX(MixOfBusinessActual[AccountingMonthSort]), ALL(MixOfBusinessActual))
VAR _AccountingYear = CALCULATE(MAX(_DateMonth[AccountingYear]), ALL(_DateMonth), _DateMonth[AccountingMonthSort] = _AccountingMonthSort)

RETURN
CALCULATE (
	SUM(MixOfBusinessActual[Revenue]),
	_DateMonth[AccountingYear] = _AccountingYear
)

Variance:

M_Revenue_Variance_MixOfBusinessActual = [M_Revenue_ThisYear_MixOfBusinessActual] - [M_Revenue_MixOfBusinessActual]

Variance %:

M_Revenue_VariancePercentage_MixOfBusinessActual = [M_Revenue_ThisYear_MixOfBusinessActual] / ABS([M_Revenue_MixOfBusinessActual])

[AlexisOlson · Accepted solution]

The quick and dirty solution is to turn off word wrapping on the values and column headers and resize the columns to be essentially invisible.

To do more properly, you'd need to create a custom header table and corresponding measures that do the appropriate switching. See here for an example of this approach.

[zfemmer]

Additional Question...

Thoughts on conditional formatting with this? I am assuming it isn't really feasible considering everything is now one "value". I can live without it, would just be a nice addition if possible.

[AlexisOlson]

You can still do rules-based conditional formatting by writing a [Color] measure that switches on your header group to use for your rules. Gradient conditional formatting might be possible but would be a headache trying to normalize revenue and variance into the same gradient.

[zfemmer]

Thanks for the example...huge help!

I am much more SQL inclined than I am DAX, so I made the "Header" table there and just imported it in with the dynamic years and such.

IF OBJECT_ID('tempdb.dbo.#Measures', 'U') IS NOT NULL DROP TABLE #Measures;

DECLARE @Date DATE
	SET @Date = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
DECLARE @AccountingYear SMALLINT
	SET @AccountingYear = ( SELECT AccountingYear FROM dbo.MasterDate WHERE Date = @Date )

CREATE TABLE #Measures
(
	MeasureName VARCHAR(25) NOT NULL,
	MeasureFormat VARCHAR(25) NOT NULL,
	[Index] SMALLINT NOT NULL
)

INSERT INTO #Measures
( MeasureName,MeasureFormat, [Index] )
VALUES
( 'Variance','\$#,0;(\$#,0);\$#,0',2 ),
( 'Variance %','0.0%;-0.0%;0.0%',3 )

SELECT DISTINCT AccountingYear AS Header, 'Revenue' AS [Group], '\$#,0;(\$#,0);\$#,0' AS MeasureFormat, 1 AS [Index]
	FROM dbo.MasterDate
		WHERE AccountingYear IN ( @AccountingYear, @AccountingYear - 1, @AccountingYear - 2 )
UNION ALL
SELECT DISTINCT Dates.AccountingYear AS Header, Measure.MeasureName AS [Group], Measure.MeasureFormat, [Index]
	FROM dbo.MasterDate Dates
	CROSS JOIN #Measures Measure
		WHERE Dates.AccountingYear IN ( @AccountingYear - 1, @AccountingYear - 2 )
			ORDER BY [Index], Header

I then wrote this SWITCH measure that gives me what I need:

VAR _AccountingMonthSort = CALCULATE(MAX(MixOfBusinessActual[AccountingMonthSort]), ALL(MixOfBusinessActual))
VAR _AccountingYear = CALCULATE(MAX(_DateMonth[AccountingYear]), ALL(_DateMonth), _DateMonth[AccountingMonthSort] = _AccountingMonthSort)

VAR _Value =
    SWITCH(
        SELECTEDVALUE( _Headers[Group] ),
            "Revenue",
                CALCULATE(
                    SUM(MixOfBusinessActual[Revenue]),
                    FILTER( MixOfBusinessActual, MixOfBusinessActual[AccountingYear] = MAX( _Headers[Header] ) )
                ),
            "Variance",
                CALCULATE (
                    SUM(MixOfBusinessActual[Revenue]),
                    _DateMonth[AccountingYear] = _AccountingYear
                ) -
                CALCULATE(
                    SUM(MixOfBusinessActual[Revenue]),
                    FILTER( MixOfBusinessActual, MixOfBusinessActual[AccountingYear] = MAX( _Headers[Header] ) )
                ),
            "Variance %",
                CALCULATE (
                    SUM(MixOfBusinessActual[Revenue]),
                    _DateMonth[AccountingYear] = _AccountingYear
                ) /
                ABS(CALCULATE(
                    SUM(MixOfBusinessActual[Revenue]),
                    FILTER( MixOfBusinessActual, MixOfBusinessActual[AccountingYear] = MAX( _Headers[Header] ) )
                ))
    )

RETURN
FORMAT(_Value,
    CALCULATE(
        MAX(_Headers[MeasureFormat]),
        FILTER (_Headers, _Headers[Group] = MAX(_Headers[Group]))))

Thanks again!