DAX Calendar Specific Measure

Overview

These are specific time period calculations that are appropriate in the following areas:

• Traditional Financial Statements. e.g. Current Period-to-date, Last Month, Previous Year-to-Date
• Sales, Inventory (or Any Detail Data Model).  e.g. Current Quarter-to-Date, Sales last 60 days
• Single Tile Metrics. e.g. Sales Yesterday
• Comparisons.  e.g. Current Month-to-Date next to Previous Year Month-to-Date

The big difference between the ? Calendar Pattern Measures and the Calendar Specific Measures is that the ? Calendar Pattern Measures cover complete time periods, e.g. Year, Month whereas the Calendar Specific Measures generally cover aggregation of data up to a "today" or "to-date" period, e.g. Year-to-Date, or Month-to-Date - OR - aggregation of a specific whole time period e.g. Current Month, Last Month, Previous Year Month.

NOTE: Today's date is configured in the QQube Configuration Tool

Subcategories - Common

Here is a list of measures which are used in almost all data models.  The majority are based upon transaction date.  However Vehicle Mileage is based upon Trip End Date, and Payroll is based upon Effective Payroll Date.

Subcategories - Extended Future Dates

These would be used (along with others) for:

• Expected Date (Open Purchase Orders)
• Ship Date (Open Sales Orders)
• Pending Builds

Subcategories - Inventory

These are used specifically in the Inventory Data Model

Examples

Current Month Actual =
IF (
    [[DAXP]] ShowValueForDates],
    VAR todayselection = [[DAXP]] Today Selection]
    VAR LastMonthAvailable =
        CALCULATE (
            MAX ( 'Calendar'[Year Month Number] ),
            'Calendar'[Date] = todayselection
        )
    VAR Result =
        CALCULATE (
            [.Hierarchy Actual],
            ALLEXCEPT (
                'Calendar',
                'Calendar'[Day Holiday Name],
                'Calendar'[Day is Working Day],
                'Calendar'[Day of Week Abbr]
            ),
            'Calendar'[Year Month Number] = LastMonthAvailable,
            ALL ( 'Calendar' )
        )
    RETURN
        Result
)

Current Month-to-Date Actual =
IF (
    [[DAXP]] ShowValueForDates],
    VAR todayselection = [[DAXP]] Today Selection]
    VAR LastMonthAvailable =
        CALCULATE (
            MAX ( 'Calendar'[Year Month Number] ),
            'Calendar'[Date] = todayselection
        )
    VAR Result =
        CALCULATE (
            [.Hierarchy Actual],
            ALLEXCEPT (
                'Calendar',
                'Calendar'[Day Holiday Name],
                'Calendar'[Day is Working Day],
                'Calendar'[Day of Week Abbr]
            ),
            'Calendar'[Year Month Number] = LastMonthAvailable,
            'Calendar'[SequentialDayNumber] <= todayselection,
            ALL ( 'Calendar' )
        )
    RETURN
        Result
)

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