Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 ) )
Proud to be a Super User!
@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 ) )
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |