Trying to figure out how ParallelPeriod works in DAX

Question

I have a simple Power BI table that looks as follows:

I have two tables. A Date table and an Invoice table with a field representing invoice amounts. This is a 1-M relationship on Invoice.InvoiceDate.

The second column is simply a measure for the sum of invoices. The third and fourth columns are measures using ParallelPeriod to sum invoices for 12 months prior and 24 months prior. Even though these numbers are correct, I'm not entirely certain I know what's actually going on.

The measure for the 12-month parallel period looks like this:

Sum Invoice Amount 12 Months Ago =
CALCULATE (
    SUM ( FactCustomerTransaction[InvoiceAmountDollars] ),
    PARALLELPERIOD ( 'Date'[Date], -12, MONTH )
)

Here is what I think is happening. When the sum is calculated for say 2015-Feb, all values for that month are retrieved in the invoice (many side) table and summed to generate the "Sum Invoice Amount". The sames dates, minus 12 months, in the Date table, are retrieved and the same sum is generated for those range of dates for 'Sum Invoice Amount 12 Months Ago'. And then the same process for 24 months ago.

This works because of the 1-M relationship between Date and Invoice. Is this correct?

Alexis Olson Alexis Olson · Accepted Answer · 2019-02-07T22:19:29

For 2015-Feb row, assuming the Year and Month Name column is on your 'Date' table, your filter context is 'Date'[Year and Month Name] = "2015-Feb". This filter corresponds to the dates 2015-02-01 through 2015-02-28 in your 'Date'[Date] column and that filtering propagates across the relationship to return only the rows in FactCustomerTransaction table where InvoiceDate is one of those dates and then sums the amounts corresponding to only those rows.

When you add PARALLELPERIOD, it works the same way except that after matching the dates 2015-02-01 through 2015-02-28 corresponding to 'Date'[Year and Month Name] = "2015-Feb", it shifts those dates back by 12 months and then propagates those shifted dates across the relationship.

Trying to figure out how ParallelPeriod works in DAX

1 Answers