Hi Community,
I have been working on a requirement where Time intelligence and Calculation group calculations are required. Please see below.
Calendar Table requirement :
My Fiscal year starts from February . I would need a Calendar table designed according to the Fiscal year start date (February 1st )
Q1 - Feb, Mar, Apr
Q2 - may,June , July
Q3 - Aug, Sept, Oct
Q4 - Nov,Dec, Jan
Note: Even though we are currently in 2024,the fiscal year should be shown as FY25
- No of Years of I want in Calendar table - 4 years
- 2022 February 1st - 1st January 2026
Sample Data
| Calendar Year | Date | Month | Fiscal Year | Quarter |
| 2024 | 2/1/2024 | Feb | 2025 | Q1 |
| 2024 | 2/2/2024 | Feb | 2025 | Q1 |
| 2024 | 2/3/2024 | Feb | 2025 | Q1 |
| 2024 | 2/4/2024 | Feb | 2025 | Q1 |
| 2024 | 2/5/2024 | Feb | 2025 | Q1 |
| 2024 | 2/6/2024 | Feb | 2025 | Q1 |
| 2024 | 2/7/2024 | Feb | 2025 | Q1 |
| 2024 | 2/8/2024 | Feb | 2025 | Q1 |
| 2024 | 3/1/2024 | mar | 2025 | Q1 |
| 2024 | 4/1/2024 | Apr | 2025 | Q1 |
| 2024 | 5/1/2024 | May | 2025 | Q2 |
Please help on designing a calendar table according to above needs .
Time Intelliegence Calculations :
Note: All calculations should be based on Current Quarter
Time Intelligence Functions I need according to above rules are :
YoY% = FY25 Q1 QTD - FY24Q1 QTD / FY24Q1 QTD
(Whenver quarter changes , Q1 should be replaced with new quarter dynamically )
Even though we use FY25 here, the values to be considered are for CY 2024
How to calculate FY25 Q1 QTD , FY24Q1 QTD ?
QoQ % = FY25 Q1 QTD / FY24 Q4 QTD
How to calculate FY25 Q1 QTD , FY24Q4 QTD ?
How to calculate FY24Q1 Full Qtr , FY24Q4 Full Qtr ?
Calculation groups :
After all those above mentioned DAX expressions are created, I would like to use Calculation groups to deal with 3 different segments i.e Measure group 1 , measure group 2 and Measure group 3
For example: If i calculate yoy% , Qoq% for one segment (say billings ) , I want to automatically calculate Yoy% , QoQ% and remaing calculations for different segment (Say revenue) like wise for 3rd segment .
Note: The measure columns that I am going to use are different for each segment
Thanks in advance
