DAX Calendar Pattern Measure

Overview

The measures within this category begin with a Calendar Icon represented as:

• Decimal: &#128197
• Hex: &#x1F4C5. 

This category of measure consists of a base Intermediate Measure, and allows you to create cumulative graphing, drill down options, or side by side period comparisons.

Within this category we also include quantity, amount, and new customer growth measures in the sales and inventory data models:

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

Examples - Calendar

Profit and Loss by Week Actual =
SUMX (
    VALUES ( 'Calendar'[Year Week Number] ),
    [[DAXP-WTD]] Cumulative by Week 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 =
SUMX (
    VALUES ( 'Calendar'[Year Quarter Number] ),
    [[DAXP-QTD]] Cumulative by Quarter Actual]
)

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

The SUMX function allows totals in a Power Pivot spreadsheet to appear.  Let's use this example of Cumulative by Month Total Miles.

With SUMX function you can tell the Power Pivot table to have a row total:

Without the SUMX function you can't have a row total - even if you attempted to turn it on in Power Pivot under the Design Tab.

The SUMX function certainly would reflect the expectations of an average user.

Examples - Calendar Growth

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