I have a table list of issues and various issue properties. One of the columns is the date the issue was closed. I would like another column in this table that tells me which Fiscal Year and Quarter (FYYYY QQ) it was closed in as well. I need help creating the measure for that. My fiscal year begins Feb 1st. Any help with this would be greatly appreciated.
1 Like
Hello @jloveday
Thank you for posting this question!
- As a first step, I suggest checking if you have a custom hierarchy for the Fiscal year created under Time dimension. We will need it to build the report.
If it’s not created yet, you can add it under Time dimension (you can read more about it in our documentation here): Custom time hierarchies
In this case, we would choose “Fiscal monthly” as the Type, “February” as the first month, and “FY2024” as the fiscal year.
FY2024 means that it will count February 2024 as part of fiscal year 2024. FY2025 would mean that February 2024 is returned as part of fiscal year 2025. Choose the one that fits the needs of your company.
- Once this hierarchy is added, you can create a new calculated measure using the formula below:
CASE WHEN
Not IsEmpty([Measures].[Issue closed date])
THEN
Cast([Time.Fiscal].[Day].DateMember(
[Measures].[Issue closed date]
).parent.parent.parent.name as string)
||" Q"||
Cast([Time.Fiscal].[Day].DateMember(
[Measures].[Issue closed date]
).parent.parent.key as string)
END
Make sure that the Formatting is set to Text → Plain , else you won’t get the result returned correctly.
- Once you select this new calculated measure (in my example, it’s called “Fiscal year”), you should be able to get a similar report:
I hope this helps!
Best,
Marita // support@eazybi.com
2 Likes
Thank you! That worked perfectly.
1 Like