Forum Discussion

MightyMicrobe's avatar
MightyMicrobe
Helper II
4 years ago

Previous Year Options for Time Intelligence

Hi everyone, I'm working on some documentation for implementing time intelligence for my team. I am a big fan of daxpatterns and everything else that SQLBI guys are doing, so I was adapting their approach to our use cases, and it works well. 

However, I'm struggling to explain the difference between these two options to calculate the Previous Year's sales -- they seem to do the same thing, but option 1 is so much simpler. 

Am I missing some differences that will pop up with some filter combinations? I want a robust measure but don't want to overcomplicate things unnecessary. Can you ELI5?

 

Option 1: My Measure

Orders PY 1 = 
IF (
    [ShowValueForDates],
    CALCULATE (
        [Orders],
        CALCULATETABLE (
            DATEADD ( 'Calendar'[Date], -1, YEAR ),
            'Calendar'[DateWithSales] = TRUE
        )
    )
)

 Option 2: Measure from daxpatterns

Orders PY 2 = 
VAR MonthsOffset = 12
RETURN
    IF (
        [ShowValueForDates],
        SUMX (
            SUMMARIZE ( 'Calendar', 'Calendar'[Year Month Number] ),
            VAR CurrentYearMonthNumber = 'Calendar'[Year Month Number]
            VAR PreviousYearMonthNumber = CurrentYearMonthNumber - MonthsOffset
            VAR DaysOnMonth =
                CALCULATE (
                    COUNTROWS ( 'Calendar' ),
                    ALLEXCEPT (
                        'Calendar',
                        'Calendar'[Year Month Number],
                        -- Year Month granularity
                        'Calendar'[Working Day],
                        -- Filter-safe Date column
                        'Calendar'[Day of week] -- Filter-safe Date column
                    )
                )
            VAR DaysSelected =
                CALCULATE ( COUNTROWS ( 'Calendar' ), 'Calendar'[DateWithSales] = TRUE )
            RETURN
                IF (
                    DaysOnMonth = DaysSelected, -- Selection of all days in the month
                    CALCULATE (
                        [Orders],
                        ALLEXCEPT ( 'Calendar', 'Calendar'[Working Day], 'Calendar'[Day of Week] ),
                        'Calendar'[Year Month Number] = PreviousYearMonthNumber
                    ),
                    -- Partial selection of days in a month
                    CALCULATE (
                        [Orders],
                        ALLEXCEPT ( 'Calendar', 'Calendar'[Working Day], 'Calendar'[Day of Week] ),
                        'Calendar'[Year Month Number] = PreviousYearMonthNumber,
                        CALCULATETABLE (
                            VALUES ( 'Calendar'[Day of Month Number] ),
                            ALLEXCEPT (
                                'Calendar',
                                'Calendar'[Day of Month Number],
                                'Calendar'[Date] 
                            ),
                            'Calendar'[Year Month Number] = CurrentYearMonthNumber,
                            'Calendar'[DateWithSales] = TRUE
                        )
                    )
                )
        )
    )