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
)
)
)
)
)
