DAX Calendar Specific Measure

Modified on Sun, 26 Jul at 4:36 AM

Overview

Calendar Specific Measures calculate defined time periods for financial statements, detail data models, single-value tiles, and period comparisons.

Common Uses

Traditional Financial Statements: Current Period-to-Date, Last Month, and Previous Year-to-Date.
Sales, Inventory, and Other Detail Data Models: Current Quarter-to-Date and Sales Last 60 Days.
Single-Tile Metrics: Sales Yesterday.
Comparisons: Current Month-to-Date beside Previous Year Month-to-Date.

Calendar Pattern Compared with Calendar Specific Measures

Measure Type	Purpose
Calendar Pattern Measures	Cover complete repeating periods such as Year, Quarter, Month, or Week.
Calendar Specific Measures	Aggregate data through a configured Today Date, such as Year-to-Date or Month-to-Date, or calculate one specific complete period such as Current Month, Last Month, or Previous Year Month.

Today Date

The Today Date used by these measures is configured in the QQube Configuration Tool.

Common Calendar Specific Measures

These measures appear in most QQube data models. Most use Transaction Date, while Vehicle Mileage uses Trip End Date and Payroll uses Effective Payroll Date.

Antecedent Measures

Measure	Definition
Last Month	Full month previous to current month; current month is based upon Today's date.
Last Month-to-Date	Month-to-date previous to current month; to-date represents the same day as Today's date. If the requested date is past the last day of the corresponding month, the last day of the month is returned.
Last Quarter	Full quarter previous to current quarter; current quarter is based upon Today's date.
Last Quarter-to-Date	Quarter-to-date previous to current quarter; to-date represents the same day as Today's date. If the requested date is past the last day of the corresponding month, the last day of the month is returned.
Last Week	Full week previous to current week; current week contains Today's date.
Last Week-to-Date	Week-to-date previous to current week; to-date represents the same weekday as Today's date.

Current Measures

Measure	Definition
Current Month	Full month with the current month containing Today's date.
Current Month-to-Date	Month through Today's date.
Current Quarter	Full quarter actual amount with the current quarter containing Today's date.
Current Quarter-to-Date	Quarter through Today's date.
Current Week	Full week with the current week containing Today's date.
Current Week-to-Date	Week through Today's date; Sunday is day one.
Current Year	Full year with the current year containing Today's date.
Current Year-to-Date	Year through Today's date.
Current Year-to-Last Month	Year through the end of the month previous to the current month; current month is based upon Today's date.

Previous Year Measures

Measure	Definition
Previous Year	Full year previous to current year; current year contains Today's date.
Previous Year Last Month	Full month, 13 months previous to current month; current month is based upon Today's date.
Previous Year Last Quarter	Full quarter, 5 quarters previous to current quarter; current month is based upon Today's date.
Previous Year Month	Full month, 12 months previous to current month; current month is based upon Today's date.
Previous Year Month-to-Date	Month-to-date 12 months previous to current month-to-date; to-date represents the same day as Today's date. If the requested date is past the last day of the corresponding month, the last day of the month is returned.
Previous Year Quarter	Full quarter, 4 quarters previous to current quarter; current quarter contains Today's date.
Previous Year Quarter-to-Date	Quarter-to-date 12 months previous to current quarter-to-date; to-date represents the same day as Today's date. If the requested date is past the last day of the corresponding month, the last day of the month is returned.
Previous Year-to-Date	Year-to-date 12 months previous to current year-to-date; to-date represents the same weekday as Today's date. If the requested date is past the last day of the corresponding month, the last day of the month is returned.
Previous Year-to-Last Month	Full month 13 months previous to current month; current month is based upon Today's date.

Today, Tomorrow, and Yesterday

Measure	Definition
Today	Today's date.
Tomorrow	Today's date plus 1 day.
Yesterday	Today's date minus 1 day.

Extended Future Date Measures

These measures can be used with future-oriented dates such as:

Expected Date for Open Purchase Orders.
Ship Date for Open Sales Orders.
Pending Builds.

Measure	Definition
Next Month	Full month previous to current month; current month is based upon Today's date.
Next Quarter	Month-to-date previous to current month; to-date represents the same day as Today's date. If the requested date is past the last day of the corresponding month, the last day of the month is returned.
Next Week	Full quarter previous to current quarter; current quarter is based upon Today's date.
Next Two Weeks	Quarter-to-date previous to current quarter; to-date represents the same day as Today's date. If the requested date is past the last day of the corresponding month, the last day of the month is returned.
Next Year

Inventory-Specific Measures

The following Calendar Specific Measures are used in the Inventory data model.

Sales Order — Original and Initial Invoiced

Measure	Definition
Last 30 Days	Full month previous to current month; current month is based upon Today's date.
Last 365 Days	Month-to-date previous to current month; to-date represents the same day as Today's date. If the requested date is past the last day of the corresponding month, the last day of the month is returned.

Sales

Measure	Definition
Last 30 Days	Full quarter previous to current quarter; current quarter is based upon Today's date.
Last 60 Days	Quarter-to-date previous to current quarter; to-date represents the same day as Today's date. If the requested date is past the last day of the corresponding month, the last day of the month is returned.

DAX Examples

Current Month Actual

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

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 )

Filter-Safe Calendar Columns

Three calendar columns are always preserved regardless of other filters applied to a measure:

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

Version 10 Addition

'Calendar'[Day is Working Day] was added in QQube Version 10.

Expected Result

Calendar Specific Measures provide ready-to-use calculations for current, prior, future, and rolling periods while respecting the Today Date configured in QQube.