DAX to display X quarter prior dates from selected dropdown value

[Anonymous]

I'm looking for a DAX command to create 4 measures to display the quarter end date which is X ( X=0,1,2,3,4) quarter prior from a selected date in dropdown. My dates column for drop-down looks something like this:

4/17/2018

4/30/2018

5/21/2018

6/30/2018

6/30/2018

7/31/2018

9/14/2018

9/30/2018

10/1/2018

So for eg if someone selects: Apr-2018 from the dropdown, the 4 measures should return:

X0= 6/30/2018 (current quarter end date)

X1= 3/31/2018 (1 quarter prior end date)

X2= 12/31/2017 (2 quarter prior end date)

X3= 30/09/2017 (3 quarter prior end date)

X4= 30/06/2017 (4 quarter prior end date)

I wrote a DAX command as below to create a measure to return previous quarter end date but it's not working:

Last Date of PQ = 
VAR myDate = MAX('Table'[Dates])
RETURN
CALCULATE( MIN( 'Table'[Dates] ), PREVIOUSQUARTER( CALCULATETABLE(VALUES( 'Table'[Dates]), 'Table'[Dates] = myDate) ) )

Please let me know if there's any correction in the command or any other ways to achieve the results. 

The pbix file with full data is attached here: https://drive.google.com/file/d/1kX9o4C_00PLMCO-j-DU9NX3ac-7tXiGP/view?usp=sharing

[DataInsights · Accepted solution]

@Anonymous,

1. Create a Date table (named Dates in this example).

2. Mark the above table as Date table.

3. Create relationships with the Date table as appropriate.

4. Create measures:

X0 = ENDOFQUARTER ( Dates[Date] )

X1 = CALCULATE ( [X0], DATEADD ( Dates[Date] , -1, QUARTER ) )

X2 = CALCULATE ( [X0], DATEADD ( Dates[Date] , -2, QUARTER ) )

X3 = CALCULATE ( [X0], DATEADD ( Dates[Date] , -3, QUARTER ) )

X4 = CALCULATE ( [X0], DATEADD ( Dates[Date] , -4, QUARTER ) )