DAX Intermediate Measure

Modified on Sat, 29 Jun at 2:29 AM

Overview

These start with [DAXP and are hidden. Their purpose is to perform specific query functions that are re-used in other formulas.

Visualization Control Measures

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.

[DAXP] Today Selection Grabs the today date as defined from the QQube Configuration Tool. Used in "to-date" measures
[DAXP] ShowValueForTransactionDates Is TRUE for dates before the last found date value and FALSE for days after the last found date value. Generally used in Calendar Pattern Measures.

Cumulative Period Measures

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.

[DAXP-WTD] Cumulative By Week Each day in a week accumulates until the end of the week.
[DAXP-MTD] Cumulative By Month Each day in a month accumulates until the end of the month.
[DAXP-QTD] Cumulative By Quarter Each day in a quarter accumulates until the end of the quarter.
[DAXP-YTD] Cumulative By Year Each day in a quarter accumulates until the end of the year.

Filter Safe Components

We use three filter-safe columns which are always preserved regardless of all other filters applied to a measure.

'Calendar'[Day Holiday Name]
'Calendar'[Day is Working Day]
'Calendar'[Day of Week Abbr]

We added 'Day is Working Day' in Version 10

Examples

[[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 <= LastDateWithData
RETURN
  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
)