How do I use the DAX function ParallelPeriod

Question

The ParaellePeriod function allows for the comparison of values between points in time (how do sales compare to a year ago). I'm doing something wrong in my use of it, but have no idea what that thing may be.

Set up

I created a bog simple PowerPivot SQL Server 2008+ source query and named it Source. The query generates 168 rows: 6 IDs (100-600) and 28 dates (first of a month from Jan 2010 to Apr 2012) all cross applied together.

; WITH SRC (groupKey, eventDate, value) AS
(
    SELECT G.groupKey, D.eventDate, CAST(rand(G.groupKey * year(D.eventDate) * month(D.eventDate)) * 100 AS int)
    FROM
    (
        SELECT 100
        UNION ALL SELECT 200
        UNION ALL SELECT 300
        UNION ALL SELECT 400
        UNION ALL SELECT 500
        UNION ALL SELECT 600
    ) G (groupKey)
    CROSS APPLY
    (
                  SELECT CAST('2010-01-01' AS date)
        UNION ALL SELECT CAST('2010-02-01' AS date)
        UNION ALL SELECT CAST('2010-03-01' AS date)
        UNION ALL SELECT CAST('2010-04-01' AS date)
        UNION ALL SELECT CAST('2010-05-01' AS date)
        UNION ALL SELECT CAST('2010-06-01' AS date)
        UNION ALL SELECT CAST('2010-07-01' AS date)
        UNION ALL SELECT CAST('2010-08-01' AS date)
        UNION ALL SELECT CAST('2010-09-01' AS date)
        UNION ALL SELECT CAST('2010-10-01' AS date)
        UNION ALL SELECT CAST('2010-11-01' AS date)
        UNION ALL SELECT CAST('2010-12-01' AS date)
        UNION ALL SELECT CAST('2011-01-01' AS date)
        UNION ALL SELECT CAST('2011-02-01' AS date)
        UNION ALL SELECT CAST('2011-03-01' AS date)
        UNION ALL SELECT CAST('2011-04-01' AS date)
        UNION ALL SELECT CAST('2011-05-01' AS date)
        UNION ALL SELECT CAST('2011-06-01' AS date)
        UNION ALL SELECT CAST('2011-07-01' AS date)
        UNION ALL SELECT CAST('2011-08-01' AS date)
        UNION ALL SELECT CAST('2011-09-01' AS date)
        UNION ALL SELECT CAST('2011-10-01' AS date)
        UNION ALL SELECT CAST('2011-11-01' AS date)
        UNION ALL SELECT CAST('2011-12-01' AS date)
        UNION ALL SELECT CAST('2012-01-01' AS date)
        UNION ALL SELECT CAST('2012-02-01' AS date)
        UNION ALL SELECT CAST('2012-03-01' AS date)
        UNION ALL SELECT CAST('2012-04-01' AS date)
    ) D (eventDate)
)
SELECT
    *
FROM
    SRC;

I added a derived column in PowerPivot using a formula I lifted from MSDN

=CALCULATE(SUM(Source[value]), PARALLELPERIOD(Source[eventDate], -1, year))

There are no errors displayed but there's never any calculated data. I've tried different intervals (-1, +1) and periods (year, month) but to no avail.

The only thing I could observe that was different between my demo and the MSDN was theirs had a separate dimension defined for the date. Easy enough to rectify so I created a Dates query with the following. This query generates a row for all the days between 2010-01-01 and 2012-06-01 (1096 rows)

DECLARE
    @start int = 20100101
,   @stop int = 20120601;

WITH L0 AS
(
    SELECT
        0 AS C
    UNION ALL
    SELECT
        0
)
, L1 AS
(
    SELECT
        0 AS c
    FROM
        L0 AS A
        CROSS JOIN L0 AS B
)
, L2 AS
(
    SELECT
        0 AS c
    FROM
        L1 AS A
        CROSS JOIN L1 AS B
)
, L3 AS
(
    SELECT
        0 AS c
    FROM
        L2 AS A
        CROSS JOIN L2 AS B
)
, L4 AS
(
    SELECT
        0 AS c
    FROM
        L3 AS A
        CROSS JOIN L3 AS B
)
, L5 AS
(
    SELECT
        0 AS c
    FROM
        L4 AS A
        CROSS JOIN L4 AS B
)
, NUMS AS
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS number
    FROM
        L5
)
,   YEARS AS
(
    SELECT 
        Y.number 
    FROM
        NUMS Y
    WHERE
        Y.number BETWEEN @start / 10000 AND @stop / 10000
)
,   MONTHS AS
(
    SELECT 
        Y.number 
    FROM
        NUMS Y
    WHERE
        Y.number BETWEEN 1 and 12
)
,   DAYS AS
(
    SELECT 
        Y.number 
    FROM
        NUMS Y
    WHERE
        Y.number BETWEEN 1 and 31
)
,  CANDIDATES_0 AS
(
    SELECT
        Y.number * 10000 + M.number * 100 + D.number AS SurrogateKey 
    ,   CAST(Y.number * 10000 + M.number * 100 + D.number AS char(8)) AS DateValue
    FROM 
        YEARS Y
        CROSS APPLY
            MONTHS M
        CROSS APPLY
            DAYS D
)
, HC AS
(
    SELECT
        Y.number * 10000 + M.number * 100 + D.number AS SurrogateKey 
    ,   CAST(Y.number * 10000 + M.number * 100 + D.number AS char(8)) AS DateValue
    FROM 
        YEARS Y
        CROSS APPLY
            MONTHS M
        CROSS APPLY
            DAYS D
    WHERE
        D.number < 31
        AND M.number IN (4,6,9,11)

    UNION ALL
    SELECT
        Y.number * 10000 + M.number * 100 + D.number AS SurrogateKey 
    ,   CAST(Y.number * 10000 + M.number * 100 + D.number AS char(8)) AS DateValue
    FROM 
        YEARS Y
        CROSS APPLY
            MONTHS M
        CROSS APPLY
            DAYS D
    WHERE
        D.number < 32
        AND M.number IN (1,3,5,7,8,10,12)

    UNION ALL
    SELECT
        Y.number * 10000 + M.number * 100 + D.number AS SurrogateKey 
    ,   CAST(Y.number * 10000 + M.number * 100 + D.number AS char(8)) AS DateValue
    FROM 
        YEARS Y
        CROSS APPLY
            MONTHS M
        CROSS APPLY
            DAYS D
    WHERE
        D.number < 29
        AND M.number = 2
        AND
        ( 
            Y.number % 4 > 0
            OR Y.number % 100 = 0 AND Y.number % 400 > 0 
        )

    UNION ALL
    SELECT
        Y.number * 10000 + M.number * 100 + D.number AS SurrogateKey 
    ,   CAST(Y.number * 10000 + M.number * 100 + D.number AS char(8)) AS DateValue
    FROM 
        YEARS Y
        CROSS APPLY
            MONTHS M
        CROSS APPLY
            DAYS D
    WHERE
        D.number < 30
        AND M.number = 2
        AND
        ( 
            Y.number % 4 = 0
            OR Y.number % 100 = 0 AND Y.number % 400 = 0 
        )
)
, CANDIDATES AS
(
    SELECT
        C.SurrogateKey
    ,   CAST(C.DateValue as date) As DateValue
    FROM
        HC C
    WHERE
        ISDATE(c.DateValue) = 1
)
, PARTS 
(
    DateKey
,   FullDateAlternateKey
,   DayNumberOfWeek
,   EnglishDayNameOfWeek
,   DayNumberOfMonth
,   DayNumberOfYear
,   WeekNumberOfYear
,   EnglishMonthName
,   MonthNumberOfYear
,   CalendarQuarter 
,   CalendarYear    
,   CalendarSemester    
--,FiscalQuarter    
--,FiscalYear   
--,FiscalSemester
) AS
(
    SELECT
        CAST(C.SurrogateKey AS int)
    ,   C.DateValue
    ,   DATEPART(WEEKDAY, C.DateValue)
    ,   DATENAME(WEEKDAY, C.DateValue)
    ,   DATEPART(DAY, C.DateValue)
    ,   DATEPART(DAYOFYEAR, C.DateValue)
    ,   DATEPART(WEEK, C.DateValue)
    ,   DATENAME(MONTH, C.DateValue)
    ,   DATEPART(MONTH, C.DateValue)
    ,   DATEPART(QUARTER, C.DateValue)
    ,   DATEPART(YEAR, C.DateValue)
    ,   DATEPART(WEEK, C.DateValue)
    FROM
        CANDIDATES C
    WHERE
        C.DateValue IS NOT NULL
)
SELECT 
    P.*
FROM 
    --HC P
    PARTS P
ORDER BY 1;

With data generated, I created a relationship between the Source and Dates and tried this formula with no luck either

=CALCULATE(SUM(Source[value]), PARALLELPERIOD(Dates[FullDateAlternateKey], -1, year))

The PowerPivot designer looks like

Any thoughts on what I'm doing wrong?

References

• PARALLELPERIOD Function
• PowerPivot DAX PARALLELPERIOD vs DATEADD

Answer 1

The DAX expression you used in the derived column should be a measure and defined in the calculation area...

MeasurePriorPeriodValue := CALCULATE(SUM(Source[value]), PARALLELPERIOD(Source[eventDate], -1, year))

...as long as the column you use in the parallelperiod function is configured as a date datatype, it should still work. Having the date table separated from the rest is "best practice" but not required...because it allows you to ensure that there are no gaps (which can cause problems with some DAX Time-Intelligence functions) and things like that.