DAX Calendar Pattern Measures

Modified on Fri, 21 Aug at 5:06 AM

Overview

QQube Calendar Pattern Measures are organized in the ?? Calendar Pattern Measures display folder.

Each Calendar Pattern Measure uses a supporting Intermediate Measure.

These measures support:

Cumulative graphing.
Calendar drill-down paths.
Side-by-side period comparisons.
Correct period totals in Power Pivot.

QQube Calendar Pattern Measures displayed in the Calendar Pattern Measures folder

Calendar Pattern Measures used in a period-based visualization

Calendar Growth Measures

Sales and Inventory data models also include calendar-based growth measures for quantity, amount, and new-customer metrics.

Month-Over-Month - MOM%
Quarter-Over-Quarter - QOQ%
Year-Over-Year - YOY%

Calendar Measure Examples

Profit and Loss by Week Actual

Profit and Loss by Week Actual =
SUMX (
    VALUES ( 'Calendar'[Year Week Number] ),
    [[DAXP-WTD]] Cumulative by Week Actual]
)

Profit and Loss by Month Actual

Profit and Loss by Month Actual =
SUMX (
    VALUES ( 'Calendar'[Year Month Number] ),
    [[DAXP-MTD]] Cumulative by Month Actual]
)

Profit and Loss by Quarter Actual

Profit and Loss by Quarter Actual =
SUMX (
    VALUES ( 'Calendar'[Year Quarter Number] ),
    [[DAXP-QTD]] Cumulative by Quarter Actual]
)

Profit and Loss by Year Actual

Profit and Loss by Year Actual =
SUMX (
    VALUES ( 'Calendar'[Year Number] ),
    [[DAXP-YTD]] Cumulative by Year Actual]
)

Why SUMX Is Used

The SUMX function evaluates the cumulative measure once for each visible calendar period and then adds those results together.

Power Pivot Totals

Using SUMX allows a Power Pivot table to display the expected row total for cumulative period measures.

Power Pivot cumulative measure showing a row total when SUMX is used

Without SUMX, the row total does not appear correctly, even when totals are enabled from the Power Pivot Design tab.

Power Pivot cumulative measure without a correct row total when SUMX is omitted

Calendar Growth Example

Sales Quantity - YOY %

Sales Quantity - YOY % =
DIVIDE (
    [Sales Quantity - Cumulative by Year],
    [Sales Quantity - Cumulative by Year PY]
)

Growth Measure Behavior

The measure divides the current cumulative yearly quantity by the prior-year cumulative quantity to produce the Year-Over-Year percentage.

Expected Result

Calendar Pattern Measures provide cumulative reporting by week, month, quarter, and year; support calendar drill-downs and side-by-side comparisons; and use SUMX to produce the expected totals in Power Pivot.