These start with [DAXP and are hidden. Their purpose is to perform specific query functions that are re-used in other formulas.
These two formulas contain logic that is re-used in other formulas to ensure that the range of data is controlled. For example, a cumulative line graph should stop at the appropriate data point - not show as a straight line after that date.
Accumulate up to end of the desired period category. The last period in that category will accumulate up until today's date as defined in the QQube Configuration Tool.
We use three filter-safe columns which are always preserved regardless of all other filters applied to a measure.
We added 'Day is Working Day' in Version 10
[[DAXP]] Today Selection] =IF ( HASONEVALUE ( Company[QQube Today Date] ), VAR TodayChoice = VALUES ( Company[QQube Today Date] ) VAR Result = TodayChoice RETURN Result, TODAY ())
[[DAXP]] ShowValueForDates] =VAR LastDateWithData = CALCULATE ( MAX ( 'PROFIT AND LOSS MEASURES'[LinkToTxnDateID] ), ALL ( 'PROFIT AND LOSS MEASURES'[LinkToTxnDateID] ) )VAR FirstDateVisible = MIN ( 'Calendar'[LinkForTxnDateID] )VAR Result = FirstDateVisible <= LastDateWithDataRETURN Result
[[DAXP-WTD]] Cumulative by Week Actual] =IF ( [[DAXP]] ShowValueForDates], VAR LastDayAvailable = MAX ( 'Calendar'[Day Number in Week] ) VAR LastWeekAvailable = MAX ( 'Calendar'[Year Week Number] ) VAR Result = CALCULATE ( [.Hierarchy Actual], ALLEXCEPT ( 'Calendar', 'Calendar'[Day Holiday Name], 'Calendar'[Day is Working Day], 'Calendar'[Day of Week Abbr] ), 'Calendar'[Day Number in Week] <= LastDayAvailable, 'Calendar'[Year Week Number] = LastWeekAvailable ) RETURN Result)
[[DAXP-MTD]] Cumulative by Month Actual] =IF ( [[DAXP]] ShowValueForDates], VAR LastDayAvailable = MAX ( 'Calendar'[Day Number in Month] ) VAR LastMonthAvailable = MAX ( 'Calendar'[Year Month Number] ) VAR LastYearAvailable = MAX ( 'Calendar'[Year Number] ) VAR Result = CALCULATE ( [.Hierarchy Actual], ALLEXCEPT ( 'Calendar', 'Calendar'[Day Holiday Name], 'Calendar'[Day is Working Day], 'Calendar'[Day of Week Abbr] ), 'Calendar'[Day Number in Month] <= LastDayAvailable, 'Calendar'[Year Month Number] = LastMonthAvailable, 'Calendar'[Year Number] = LastYearAvailable ) RETURN Result)
[[DAXP-QTD]] Cumulative by Quarter Actual] =IF ( [[DAXP]] ShowValueForDates], VAR LastDayAvailable = MAX ( 'Calendar'[Day Number in Year] ) VAR LastFiscalYearQuarterAvailable = MAX ( 'Calendar'[Year Quarter Number] ) VAR LastYearAvailable = MAX ( 'Calendar'[Year Number] ) VAR Result = CALCULATE ( [.Hierarchy Actual], ALLEXCEPT ( 'Calendar', 'Calendar'[Day Holiday Name], 'Calendar'[Day is Working Day], 'Calendar'[Day of Week Abbr] ), 'Calendar'[Day Number in Year] <= LastDayAvailable, 'Calendar'[Year Quarter Number] = LastFiscalYearQuarterAvailable, 'Calendar'[Year Number] = LastYearAvailable ) RETURN Result)
[[DAXP-YTD]] Cumulative by Year Actual] =IF ( [[DAXP]] ShowValueForDates], VAR LastDayAvailable = MAX ( 'Calendar'[Day Number in Year] ) VAR LastYearAvailable = MAX ( 'Calendar'[Year Number] ) VAR Result = CALCULATE ( [.Hierarchy Actual], ALLEXCEPT ( 'Calendar', 'Calendar'[Day Holiday Name], 'Calendar'[Day is Working Day], 'Calendar'[Day of Week Abbr] ), 'Calendar'[Day Number in Year] <= LastDayAvailable, 'Calendar'[Year Number] = LastYearAvailable ) RETURN Result)
Please enable JavaScript to use file uploader.
is requesting access to a wiki that you have locked: https://clearify.com/wiki/view/6127/intermediate-measures
Your session has expired. You are being logged out.