How to write DAX for dynamic sales change and growth rate in drill mode with time intelligence

   Check list of all posts

This document is intended to share two DAX scripts for drilling up and down ( Day - Fiscal Month - Fiscal Quarter - Fiscal Year). It is not a simple aggregation; instead, it must satisfy a few special requirements below, which makes DAX complicated and robust at the same time.

#1. The date dimension is not standard calendar based, it is fiscal year based, along with 4-4-5.

#2. The beginning date is either selected by the date slicer or from the available date. In other words, it doesn't start from the first day of each month, quarter, or year. The same is for the ending date too. This makes many time intelligence functions invalid.

#3. The measure [Sales Amount Change] is based on Sales Amount difference from different levels ( Day - Fiscal Month - Fiscal Quarter - Fiscal Year). Furthermore, The measure [Sales Amount] can be specified as "Average" or "Spot" from end users.

#4. The measure [Growth Rate] is an annualized growth rate based on account Balance. It is calculated with different formulas at different levels.

#5. The measure [Growth Rate] must not show if the quarter doesn't have a full three months of account balance available.  

#5. The measure [Growth Rate] must present the overall rate on whatever dates are available for the whole page and the rate on Chart/Table/Metrix at the Fiscal Month and Fiscal Quarter level.  

Sales Amount =

VAR bAverageOrSpot =
    SELECTEDVALUE ( View[View] )
RETURN
    IF (
        bAverageOrSpot = "Average",
        AVERAGEX (
            VALUES ( 'Date'[Date] ),
            IF (
                ISBLANK ( [Sales] ),
                BLANK (),
                [Sales]
            )
        ),
        CALCULATE (
            IF (
                ISBLANK ( [Sales] ),
                BLANK (),
                [Sales]
            ),
            LASTDATE ( 'Date'[Date] )
        )
    )

Sales Amount Change =
VAR CurrentValue = [Sales Amount]
VAR PrevValue =
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Date'[Date] ),
            CALCULATE (
                [Sales Amount],
                DATEADD (
                    'Date'[Date],
                    -1,
                    DAY
                )
            ),
        ISINSCOPE ( 'Date'[Fiscal Year Month] ),
            VAR CurrentMONTH =
                MIN ( 'Date'[Fiscal YYYYMM] )
            VAR PrevMONTH =
                CALCULATE (
                    MAX ( 'Date'[Fiscal YYYYMM] ),
                    ALL ( 'Date' ),
                    'Date'[Fiscal YYYYMM] < CurrentMONTH
                )
            VAR PrevValueMONTH =
                CALCULATE (
                    [Sales Amount],
                    ALL ( 'Date' ),
                    'Date'[Fiscal YYYYMM] = PrevMonth
                )
            RETURN
                PrevValueMONTH,
        ISINSCOPE ( 'Date'[Fiscal Year Quarter] ),
            VAR CurrentQUARTER =
                MIN ( 'Date'[Fiscal YYYYQQ] )
            VAR PrevQUARTER =
                CALCULATE (
                    MAX ( 'Date'[Fiscal YYYYQQ] ),
                    ALL ( 'Date' ),
                    'Date'[Fiscal YYYYQQ] < CurrentQUARTER
                )
            VAR PrevValueQUARTER =
                CALCULATE (
                    [Sales Amount],
                    ALL ( 'Date' ),
                    'Date'[Fiscal YYYYQQ] = PrevQUARTER
                )
            RETURN
                PrevValueQUARTER,
        ISINSCOPE ( 'Date'[Fiscal Year] ),
            VAR CurrentYEAR =
                MIN ( 'Date'[Fiscal Year] )
            VAR PrevYEAR =
                CALCULATE (
                    MAX ( 'Date'[Fiscal Year] ),
                    ALL ( 'Date' ),
                    'Date'[Fiscal Year] < CurrentYEAR
                )
            VAR PrevValueYEAR =
                CALCULATE (
                    [Sales Amount],
                    ALL ( 'Date' ),
                    'Date'[Fiscal Year] = PrevYEAR
                )
            RETURN
                PrevValueYEAR
    )
VAR result =
    IF (
        ISBLANK ( CurrentValue )
            || ISBLANK ( PrevValue ),
        BLANK (),
        CurrentValue - PrevValue
    )
RETURN
    result

Growth Rate =

VAR _NumberOfSelectedMonths =
    COUNTROWS (
        VALUES ( 'Date'[Fiscal Year Month] )
    )
VAR _NumberOfSelectedQuarter =
    COUNTROWS (
        VALUES ( 'Date'[Fiscal Year Quarter] )
    )
VAR _NumberOfMonths =
    CALCULATE (
        COUNTROWS (
            VALUES ( 'Date'[Fiscal Year Month] )
        ),
        'Date'[Valid Date Range] = 1,
        REMOVEFILTERS ( 'Date' ),
        VALUES ( 'Date'[Fiscal Year Quarter] )
    )
VAR _BeginDate1 =
    MIN ( 'Date'[Date] )
VAR _BeginDate2 = _BeginDate1 - 1
VAR _BeginBalance2 =
    CALCULATE (
        IF (
            ISBLANK ( [Account Balance] ),
            BLANK (),
            [Account Balance]
        ),
        'Date'[Date] = _BeginDate2
    )
VAR _BeginDate =
    IF (
        ISBLANK ( _BeginBalance2 ),
        _BeginDate1,
        _BeginDate2
    )
VAR _BeginBalance =
    CALCULATE (
        IF (
            ISBLANK ( [Account Balance] ),
            BLANK (),
            [Account Balance]
        ),
        'Date'[Date] = _BeginDate
    )
VAR _EndDate =
    MAX ( 'Date'[Date] )
VAR _EndBalance =
    CALCULATE (
        IF (
            ISBLANK ( [Account Balance] ),
            BLANK (),
            [Account Balance]
        ),
        'Date'[Date] = _EndDate
    )
VAR _DaysHeld =
    DATEDIFF (
        _BeginDate,
        _EndDate,
        DAY
    )
VAR _CumulativeReturn =
    IF (
        _BeginBalance = 0
            || _DaysHeld = 0,
        BLANK (),
        ( _EndBalance - _BeginBalance ) / _BeginBalance
    )
VAR _AnnualizedReturn =
    IF (
        _BeginBalance = 0
            || _DaysHeld = 0,
        BLANK (),
        SWITCH (
            TRUE (),
            ISFILTERED ( 'Date'[Fiscal Year Month] )
                && _NumberOfSelectedMonths = 1,
                POWER (
                    ( 1 + _CumulativeReturn ),
                    12
                ) - 1,
            ISFILTERED ( 'Date'[Fiscal Year Quarter] )
                && _NumberOfSelectedMonths = 3
                && _NumberOfSelectedQuarter = 1,
                POWER (
                    ( 1 + _CumulativeReturn ),
                    4
                ) - 1,
            ISFILTERED ( 'Date'[Fiscal Year Quarter] )
                && _NumberOfMonths < 3
                && _NumberOfSelectedQuarter = 1, BLANK (),
            POWER (
                ( 1 + _CumulativeReturn ),
                ( 365.0 / _DaysHeld )
            ) - 1
        )
    )
VAR result =
    IF (
        ISBLANK ( _AnnualizedReturn ),
        "n/a",
        FORMAT (
            _AnnualizedReturn,
            "Percent"
        )
    )
RETURN
    result