DAX

What and when: DateAdd vs ParallelPeriod vs SamePeriodLastYear

~ Vitalie Ciobanu

Reza Rad over at http://radacad.com/dateadd-vs-parallelperiod-vs-sameperiodlastyear-dax-time-intelligence-question explains the differences and provides easy usage scenarios for DateAdd, ParallelPeriod and SamePeriodLastYear DAX functions.

Read his blog post to see how he reached to below conclusion:

  • DateAdd and SamePeriodLastYear both work based on the DYNAMIC period in the filter context.
  • ParallelPeriod is working STATICALLY based on the interval selected in the parameter.
  • ParallelPeriod and DateAdd can go more than one interval back and forward, while SamePeriodLastYear only goes one year back.
  • DateAdd works on the interval of DAY, as well as month, quarter and year, but ParallelPeriod only works on month, quarter, and year.
  • Depends on the filter context you may get a different result from these functions. If you get the same result in a year level context, it doesn’t mean that all these functions are the same! Look more into the detailed context.

Expressions that yield variant data-type cannot be used to define calculated columns.

~ Vitalie Ciobanu

Error:

“Expressions that yield variant data-type cannot be used to define calculated columns.“

Cause:

IF based Calculated Columns in Power BI or Power Pivot with 2 different data types is not allowed.

Solution:

Format one data type as needed using FORMAT() function.

[code language=”sql”]

Age = IF (
TableName[Birthday] < TODAY(),
FORMAT (
Year ( TODAY() )- Year ( TableName[Birthday] ),"General Number" ),
"Invalid birth date"
)

[/code]

FORMAT Function (DAX): https://msdn.microsoft.com/en-us/query-bi/dax/format-function-dax

Pre-Defined Numeric Formats for the FORMAT function: https://msdn.microsoft.com/query-bi/dax/pre-defined-numeric-formats-for-the-format-function