Need help with MDX expression

Questions & Answers
1

Hello Team,

I want to create a new calculated measure named “Quarter”. It should calculate the Quarter Value with the following consideration:
If the due date is 30 September, Qtr should be Q4
If the due date is 30 June, Qtr should be Q3
If the due date is 31 March, Qtr should be Q2
If the due date is 31 December, Qtr should be Q1

The Quarter Calculation should be done at Initiative Level only. For Epic and Story issue type, Quarter should display as blank or NA.

2

@zane.baranovska Could you help me on this?

3

@nauris.malitis Could you help me on this?

I tried the below but it didn’t work:
IIF(
[Issue].CurrentHierarchyMember.Level.Name = “Initiative”,
CASE
WHEN [Issue].CurrentHierarchyMember.get(‘Due date’) = “Sep 30 2024” THEN “Q4”
WHEN [Issue].CurrentHierarchyMember.get(‘Due date’) = “Jun 30 2024” THEN “Q3”
WHEN [Issue].CurrentHierarchyMember.get(‘Due date’) = “Mar 31 2024” THEN “Q2”
ELSE “No Match”
END,
[Measures].[Issue due date]
)

4

@nauris.malitis could you help me on the above MDX ?

5

Hi

You may use the following calculation to return the quarter name based on the due date.

CASE WHEN
[Issue].CurrentHierarchyMember.Level.Name = "Initiative"
THEN
[Time].[Quarter].DateMember(
DateAddDays(
[Issue].CurrentHierarchyMember.Get("Due date"),
1)
).Name
END

The calculation checks the initiative’s due date and returns the quarter of the next day; if the due date falls on Sep 30, Jun 30, Mar 31, or Dec 31, the retrieved quarter is from the next quarter. Otherwise, the quarter is the default one.

Best,
Ilze, support@eazybi.com