Forum Discussion
Helper IHow To Hide Variances For This Year
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.
Super UserThe 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.
Helper IThanks 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!