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 have created a Date table with Date column with calenderauto(6) format.
Date table Date column is created from the Data view -> Table tools -> calculations group -> New Table
and
quarter column is a calculated column from the Data view -> Table tools -> calculations group -> New Column
Date(mm/dd/yyyy) | quarter |
7/1/2020 | FY2021 Q1 |
9/30/2020 | FY2021 Q1 |
10/1/2020 | FY2021 Q2 |
12/31/2020 | FY2021 Q2 |
1/1/2021 | FY2021 Q3 |
3/31/2021 | FY2021 Q3 |
4/1/2021 | FY2021 Q4 |
6/30/2021 | FY2021 Q4 |
Quarter('Date'[Date]) formula is calculating the quarters in the fiscal year format Jan to Dec.
But I need to display the quarter values in Fiscal Year format of July2020 to June 2021
July-Sep -- Q1,
Oct -dec -- Q2,
Jan- Mar -- Q3,
Apr-Jun -- Q4
Hi, @Vid , you may try to add a calculated column this way,
FY Qtr = var __fy_date = EDATE([Date(mm/dd/yyyy)], 6) return "FY" & YEAR(__fy_date) & " Q" & QUARTER(__fy_date)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi @CNENFRNL ,
Thanks for the solution. It is working fine.
I wrote another formula, Which is also working.
But which one is better for performance for a calculated column?
--------------------------------------------------
Quarter =
"FY" &
YEAR(Date''[Date]) + IF(MONTH('Date'[Date])>6, 1)
& " Q" &
IF(MONTH('Date'[Date]) <=3, 3,
IF(MONTH('Date'[Date]) <=6, 4,
IF(MONTH('Date'[Date]) <=9, 1, 2)
----------------------------------------
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |