Returning data for quarter in prior year

[cv77]

Hello,

I am trying to determine how to use the parallelperiod function (or dateadd, or anything else that will work) to perform a calculation for a selected date interval, e.g. for a quarter, and the same calculation for the prior date interval.  

I have a date dimension table with values in a QUARTER column like this: 

01/01/2019

04/01/2019

07/01/2019

10/01/2019

01/01/2020

04/01/2020

07/01/2020

10/01/2020

I have a slicer with these quarters (and calendar years) which I intend the user to use to select the date interval they want to look at.  

I have a measure with this: 

tester = CALCULATE(DISTINCTCOUNT(MYTBL[TR_ID]), PARALLELPERIOD(DIM_DATE[QUARTER],-1,QUARTER))

This works when the selected quarter is not the first quarter of the year.  E.g. when 04/01/2020 is selected, the measure above returns  the calculation for 01/01/2020.  However if 01/01/2020 is selected in the slicer, a blank value is returned.  I thought maybe it was because the slicer is filtering the results to show only 2020.  So I added a REMOVEFILTERS on the above measure to remove the DIM_DATE[QUARTER] filter from the card on which I was showing the results, but I still get a blank value. 

The same thing happens if the interval is "1" rather than "-1" and I select the last quarter in the year.  

How is PARALLELPERIOD meant to be used to return a value outside the current filter context?  

Thanks. 

[jdbuchanan71 · Accepted solution]

@cv77 

When you are using the time intelligent functions you should always be referencing the DATE column of your date table.

tester =
CALCULATE (
    DISTINCTCOUNT ( MYTBL[TR_ID] ),
    PARALLELPERIOD ( DIM_DATE[DATE], -1, QUARTER )
)

Some notes on the requirements of the date table from https://dax.guide/parallelperiod/

In order to use any time intelligence calculation, you need a well-formed date table. The Date table must satisfy the following requirements:

• All dates need to be present for the years required. The Date table must always start on January 1 and end on December 31, including all the days in this range. If the report only references fiscal years, then the date table must include all the dates from the first to the last day of a fiscal year. For example, if the fiscal year 2008 starts on July 1, 2007, then the Date table must include all the days from July 1, 2007 to June 30, 2008.
• There needs to be a column with a DateTime or Date data type containing unique values. This column is usually called Date. Even though the Date column is often used to define relationships with other tables, this is not required. Still, the Date column must contain unique values and should be referenced by the Mark as Date Table feature. In case the column also contains a time part, no time should be used – for example, the time should always be 12:00 am.
• The Date table must be marked as a date table in the model, in case the relationship between the Date table and any other table is not based on the Date.

The way time intelligent functions work is like this

• When you select '01/01/2021' in the [Quarter] field on your DIM_DATE table what you have really selected is the 90 dates between 1/1/2021 and 3/31/2021

1/1/2021

1/2/2021

...

3/30/2021

3/31/2021

• The time intelligence function takes that list of dates and shifts it based on the conditions you set so in your case of PARALLELPERIOD ( DIM_DATE[DATE], -1, QUARTER ) it takes that list of dates and shift them back 1 quarter.  This new list of dates it the filter context which is used to evaluate your DISTINCTCOUNT ( MYTBL[TR_ID] )

[cv77]

Thank you for the very thorough explanation - this helps me a lot, and it is now working the way I want. 

The two things I was doing wrong were 1) I did not know that I needed to mark the table as a date table, and 2) I should have been selecting my date column for the parallel period date because it is unique, rather than my QUARTER column which is not unique.  

Best regards,

Carolyn