ParallelPeriod not Working as Expected

[jdballard30]

I am using ParallelPeriod and not getting the results I expect. My data is in the table below. I also have a date table, and I marked it as a date table. My date slicer is set to all dates (from 5/10/2014 - 9/22/2017), but it doesn't matter what I set it to. I have a measure, Qty This Month which is calculating correctly (or at least what I expect, 37.15):

Qty This Month = TOTALMTD(SUM('Sales'[Quantity]), 'Date'[Date])

What's not working is my measure with PARALLELPERIOD, which I'm using to attempt to get the previous month quantity. 

Prev Month Qty (Parallel Period) = CALCULATE(SUM('Sales'[Quantity]), PARALLELPERIOD('Date'[Date], -1, MONTH))

It returns 448.36, which is the sum of everything except the 37.15 from the current month, and I'm expecting 154.57. I've also tried using DATEADD and PREVIOUSMONTH. The former returns the same as PARALLELPERIOD and the latter returns blank.

It's been a long week, and I'm sure it's something silly, but I'd appreciate any insights. There's a working copy of the file here, named ParallelPeriod.pbix.

CustomerId    Quantity  SaleDate

CUSTOMER2	17.12	10/1/2016
CUSTOMER2	17.37	10/1/2016
CUSTOMER2	16.92	10/1/2016
CUSTOMER2	18.73	12/9/2016
CUSTOMER2	17.04	12/9/2016
CUSTOMER2	17.56	12/9/2016
CUSTOMER2	18.1	12/23/2016
CUSTOMER2	18.53	12/23/2016
CUSTOMER2	16.58	2/10/2017
CUSTOMER2	18.05	2/10/2017
CUSTOMER2	21.98	3/17/2017
CUSTOMER2	17.45	3/17/2017
CUSTOMER2	20.78	6/1/2017
CUSTOMER2	20.86	6/1/2017
CUSTOMER2	18.46	7/31/2017
CUSTOMER2	18.26	8/10/2017
CUSTOMER2	19.08	8/10/2017
CUSTOMER2	19.26	8/10/2017
CUSTOMER2	18.38	8/10/2017
CUSTOMER2	19.98	8/18/2017
CUSTOMER2	19.3	8/18/2017
CUSTOMER2	17.88	8/24/2017
CUSTOMER2	20.8	8/24/2017
CUSTOMER2	19.89	8/24/2017
CUSTOMER2	18.04	9/6/2017
CUSTOMER2	19.11	9/7/2017

[edhans]

I think your formula is fine.

The problem is you are telling DAX to calculate the total of the previous month when the date range is 5/1/2014 - 9/22/2017, so it is adding everything up from 4/1/2014 - 8/31/2017.

If you want it to return the previous month from the last selected month, try this:

Penultimate Month Quantity =
CALCULATE (
    [Total Quantity],
    FILTER (
        'Date',
        'Date'[Month Number] = MONTH ( EOMONTH ( MAX ( 'Date'[Date] ), -1 ) )
    )
)

By the way, this is ugly and I don't like it, but it works. I'd spend another half hour making it more logical but I don't have another half hour right now. 

I recommend you put a temporary TABLE or MATRIX on your page that has the following:
YearMonth field

Total Quantity Measure:

Total Quantity = SUM(Sales[Quantity])

Previous Month Quantity Measure:

Previous Month Quantity = 
CALCULATE(
    [Total Quantity],
    PARALLELPERIOD('Date'[Date],-1,MONTH)
)

You can watch the table grow/shrink as you move the slider, and can see what the Penulatament Month Quantity should be.

[jdballard30 · Accepted solution]

After dinner out with my wife and friends, coming home and having a whiskey, it crossed my mind to try:

Prev Month Qty (Parallel Period) = CALCULATE([Qty This Month], PARALLELPERIOD('Date'[Date], -1, MONTH))

The difference is putting the measure [Qty This Month] as the first argument instead of SUM('Sales'[Quantity]) in the CALCULATE function. This calculated precisely the result I wanted! And, I realized I've done something similar before. I think I just needed to step away for a bit.

My reasoning is that if you don't use an existing measure which uses time intelligence as the first argument (such as MTD), it has no idea what period it should parallel. By specifying the original measure with MTD, it knows what month and can determine what the parallel period is.