Check list of all posts
I(Inspired by insights from DAX Patterns by Alberta and Marco)
The challenge of modeling monthly DAX calculations arises when we are dealing with a non-standardized calendar table. In a typical monthly calendar table, each record represents one Month, resulting in only 12 records for each year. Unfortunately, standard DAX date functions aren’t tailored to work efficiently with this dimension.
Date dimension
Our solution lies in constructing an optimally structured table that easily facilitates the recognition of relative periods. Refer to the Data table below, which elucidates the effectiveness of this approach.
There are three critical columns in this table:
YYYYMM: This unique key serves as an identifier for the date dimension.
YearMonth#: Calculated as the product of the year and 12, plus the month number and minus 1, this magic number enables straightforward period calculations.
YearQuarter#: Another magic number obtained by multiplying the year by 4 , adding the quarter number, and then minus 1.
Introducing these numbers simplifies period calculations significantly. For instance, to retrospect by five months, one would need to subtract 5 from the YearMonth#. If the current Month is 202309, then the YearMonth# is 2023*12 + 9 - 1 = 24284, then 5 months ago should be 24284 - 5 = 24279, which is 202304.
DAX Month date-related functions:Various scenarios and complex functions might appear daunting; however, the examples outlined here aim to demystify these complexities, offering clear and actionable steps for future undertakings.
YTD: Year-to-Date:
Sales YTD =
VAR LastMonthAvailable =
MAX ( 'Date'[YearMonth#] )
VAR LastYearAvailable =
MAX ( 'Date'[Calendar Year] )
VAR Result =
CALCULATE (
[Sales Amount],
REMOVEFILTERS ( 'Date' ),
'Date'[YearMonth#] <= LastMonthAvailable,
'Date'[Calendar Year] = LastYearAvailable
)
RETURN
Result
QTD: Quarter-to-Date:
Sales QTD =
VAR LastMonthAvailable =
MAX ( 'Date'[YearMonth#] )
VAR LastYearQuarterAvailable =
MAX ( 'Date'[YearQuarter#] )
VAR Result =
CALCULATE (
[Sales Amount],
REMOVEFILTERS ( 'Date' ),
'Date'[YearMonth#] <= LastMonthAvailable,
'Date'[YearQuarter#] = LastYearQuarterAvailable
)
RETURN
Result
MAT: Moving annual total:
Sales MAT =
VAR MonthsInRange = 12
VAR LastMonthRange =
MAX ( 'Date'[YearMonth#] )
VAR FirstMonthRange =
LastMonthRange - MonthsInRange + 1
VAR Result =
CALCULATE (
[Sales Amount],
REMOVEFILTERS ( 'Date' ),
'Date'[YearMonth#] >= FirstMonthRange
&& 'Date'[YearMonth#] <= LastMonthRange
)
RETURN
Result
PY: Previous Year:
Sales PY =
VAR CurrentYearNumber = SELECTEDVALUE ( 'Date'[Calendar Year] )
VAR PreviousYearNumber = CurrentYearNumber - 1
VAR Result =
CALCULATE (
[Sales Amount],
REMOVEFILTERS ( 'Date' ),
'Date'[Calendar Year] = PreviousYearNumber,
VALUES ( 'Date'[Calendar Month Number] )
)
RETURN
Result
PQ: Previous Quarter:
Sales PQ =
VAR CurrentYearQuarterNumber = SELECTEDVALUE ( 'Date'[YearQuarter#] )
VAR PreviousYearQuarterNumber = CurrentYearQuarterNumber - 1
VAR Result =
CALCULATE (
[Sales Amount],
REMOVEFILTERS ( 'Date' ),
'Date'[YearQuarter#] = PreviousYearQuarterNumber,
VALUES ( 'Date'[Calendar Month In Quarter Number] )
)
RETURN
Result
PM: Previous Month:
Sales PM =
VAR CurrentYearMonthNumber = SELECTEDVALUE ( 'Date'[YearMonth#] )
VAR PreviousYearMonthNumber = CurrentYearMonthNumber - 1
VAR Result =
CALCULATE (
[Sales Amount],
REMOVEFILTERS ( 'Date' ),
'Date'[YearMonth#] = PreviousYearMonthNumber
)
RETURN
Result
PP: Previous Period; automatically selects year, quarter, or month
Sales PP =
SWITCH (
TRUE,
ISINSCOPE ( 'Date'[Calendar Year Month] ), [Sales PM],
ISINSCOPE ( 'Date'[Calendar Year Quarter] ), [Sales PQ],
ISINSCOPE ( 'Date'[Calendar Year] ), [Sales PY]
)
PYMAT: Previous Moving annual total
Sales PYMAT =
VAR MonthsInRange = 12
VAR LastMonthRange =
MAX ( 'Date'[YearMonth#] ) - MonthsInRange
VAR FirstMonthRange =
LastMonthRange - MonthsInRange + 1
VAR Result =
CALCULATE (
[Sales Amount],
REMOVEFILTERS ( 'Date' ),
'Date'[YearMonth#] >= FirstMonthRange
&& 'Date'[YearMonth#] <= LastMonthRange
)
RETURN
Result
YOY: Year Over Year
Sales YOY =
VAR ValueCurrentPeriod = [Sales Amount]
VAR ValuePreviousPeriod = [Sales PY]
VAR Result =
IF (
NOT ISBLANK ( ValueCurrentPeriod )
&& NOT ISBLANK ( ValuePreviousPeriod ),
ValueCurrentPeriod - ValuePreviousPeriod
)
RETURN
Result
QOQ: Quarter Over Quarter
Sales QOQ =
VAR ValueCurrentPeriod = [Sales Amount]
VAR ValuePreviousPeriod = [Sales PQ]
VAR Result =
IF (
NOT ISBLANK ( ValueCurrentPeriod )
&& NOT ISBLANK ( ValuePreviousPeriod ),
ValueCurrentPeriod - ValuePreviousPeriod
)
RETURN
Result
MOM: Month Over Month
Sales MOM =
VAR ValueCurrentPeriod = [Sales Amount]
VAR ValuePreviousPeriod = [Sales PM]
VAR Result =
IF (
NOT ISBLANK ( ValueCurrentPeriod )
&& NOT ISBLANK ( ValuePreviousPeriod ),
ValueCurrentPeriod - ValuePreviousPeriod
)
RETURN
Result
MATG: Moving annual total growth
Sales MATG =
VAR ValueCurrentPeriod = [Sales MAT]
VAR ValuePreviousPeriod = [Sales PYMAT]
VAR Result =
IF (
NOT ISBLANK ( ValueCurrentPeriod )
&& NOT ISBLANK ( ValuePreviousPeriod ),
ValueCurrentPeriod - ValuePreviousPeriod
)
RETURN
Result
POP: Period over period; automatically selects year, quarter, or month
Sales POP =
SWITCH (
TRUE,
ISINSCOPE ( 'Date'[Calendar Year Month] ), [Sales MOM],
ISINSCOPE ( 'Date'[Calendar Year Quarter] ), [Sales QOQ],
ISINSCOPE ( 'Date'[Calendar Year] ), [Sales YOY]
)
PYTD: Previous year to date
Sales PYTD =
VAR LastMonthInYearAvailable =
MAX ( 'Date'[Calendar Month Number] )
VAR LastYearAvailable = SELECTEDVALUE ( 'Date'[Calendar Year] )
VAR PreviousYearAvailable = LastYearAvailable - 1
VAR Result =
CALCULATE (
[Sales Amount],
REMOVEFILTERS ( 'Date' ),
'Date'[Calendar Month Number] <= LastMonthInYearAvailable,
'Date'[Calendar Year] = PreviousYearAvailable
)
RETURN
Result
PQTD: Previous quarter to date
Sales PQTD =
VAR LastMonthInQuarterAvailable =
MAX ( 'Date'[Calendar Month In Quarter Number] )
VAR LastYearQuarterAvailable =
MAX ( 'Date'[YearQuarter#] )
VAR PreviousYearQuarterAvailable = LastYearQuarterAvailable - 1
VAR Result =
CALCULATE (
[Sales Amount],
REMOVEFILTERS ( 'Date' ),
'Date'[Calendar Month In Quarter Number] <= LastMonthInQuarterAvailable,
'Date'[YearQuarter#] = PreviousYearQuarterAvailable
)
RETURN
Result
YOYTD: Year over year to date
Sales YOYTD =
VAR ValueCurrentPeriod = [Sales YTD]
VAR ValuePreviousPeriod = [Sales PYTD]
VAR Result =
IF (
NOT ISBLANK ( ValueCurrentPeriod )
&& NOT ISBLANK ( ValuePreviousPeriod ),
ValueCurrentPeriod - ValuePreviousPeriod
)
RETURN
Result
QOQTD: Quarter over Quarter to date
Sales QOQTD =
VAR ValueCurrentPeriod = [Sales QTD]
VAR ValuePreviousPeriod = [Sales PQTD]
VAR Result =
IF (
NOT ISBLANK ( ValueCurrentPeriod )
&& NOT ISBLANK ( ValuePreviousPeriod ),
ValueCurrentPeriod - ValuePreviousPeriod
)
RETURN
Result
YTDOPY: Year to date Over the previous year
Sales YTDOPY =
VAR ValueCurrentPeriod = [Sales YTD]
VAR ValuePreviousPeriod = [Sales PYC]
VAR Result =
IF (
NOT ISBLANK ( ValueCurrentPeriod )
&& NOT ISBLANK ( ValuePreviousPeriod ),
ValueCurrentPeriod - ValuePreviousPeriod
)
RETURN
Result
QTDOPQ: Quarter to date Over the previous Quarter
Sales QTDOPQ =
VAR ValueCurrentPeriod = [Sales QTD]
VAR ValuePreviousPeriod = [Sales PQC]
VAR Result =
IF (
NOT ISBLANK ( ValueCurrentPeriod )
&& NOT ISBLANK ( ValuePreviousPeriod ),
ValueCurrentPeriod - ValuePreviousPeriod
)
RETURN
Result
No comments:
Post a Comment