First Transition in First day of Current Month

Questions & Answers
1

Hello every one I have an MDX calculation for Issue Status for First day of current month. I need to have new Measure which is shows First transition issue Status in first day of current month.

This is my current MDX :

CACHE ( CASE WHEN
not IsEmpty([Issue].CurrentHierarchyMember.Get(‘Status updated at’))
THEN
Filter(
[Transition Status].[Transition Status].Members,
( [Time].[Day].DateMember([Time].[Month].CurrentDateMember.PrevMember.startdate),
[Measures].[Issues history] )>0).item(0).getCaption
END
)

2

Hi,

The following formula finds the name of the status which had the earliest transition on the first day of the month:

Filter(
  [Transition Status].[Transition Status].Members,
  (([Measures].[Transition to status first timestamp],
  [Time].[Day].Datemember('first day of month'))
  =
  ([Measures].[Transition to status first timestamp],
   [Transition Status].DefaultMember,
   [Time].[Day].Datemember('first day of month')
   )
   )
).Item(0).GetCaption

If there will be no transition on that day, the result shows #null

Kindly,
Janis, eazyBI support

1 Like
3

Thank you Janis It seems it works for me :slight_smile: