Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
)
)
)
)
)
@MightyMicrobe , Please share link you have taken the second code
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
104 | |
77 | |
68 | |
61 |
User | Count |
---|---|
148 | |
107 | |
107 | |
83 | |
70 |