Need to get issues after 1 march 2018 and before this current month

Questions & Answers
1

I need to get issues after 1 March 2018 and before this current month so I made this formula but 1 May is showing also and I don’t need it to be shown

The period I need is from 01 March until 30 April 2018

Sum(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
(DateBetween([Issue].CurrentHierarchyMember.get(‘Closure Date’),
‘01 Mar 2018’, ‘first day of current month’))
and [Measures].[Issue status] = ‘Closed’
and [Measures].[Issue type] = ‘CR’))

Thanks

2

Haven’t tried it, but wouldn’t ‘last day of last month’ work?
Or even, ‘30 Apr 2018’?

3

No it didn’t work. It gets me nothing

4

30 Mar 2018 and 30 Apr 2018 works, I checked it now.

To have something relative for the last day of the last month I can’t figure it out.

5

Yes sure it will work but I need to be automatically calculated not me every month go and update it

6

Hi!

Unfortunately ‘last month’ is interpreted as the first day of previous month, so this will not work. For now, there is no option for relative date description of how to get the ‘last day of the previous month’. A good alternative for a calculated member is

DateSerial(Year(Now()), Month(Now()), 0)

The 0 date in DateSerial(…) function returns previous day.

I would also suggest that you filter Time dimension members and sum the ‘Issues with closure date’ measure. Filtering Time dimension would be faster than going through all issues.

Sum(Filter(
  [Time].[Day].Members,
  DateBetween(
    [Time].CurrentMember.StartDate,
    '1 Mar 2018', 
    DateSerial(Year(Now()), Month(Now()), 0) 
))
, ([Measures].[Issues with closure date], [Status].[Closed], [Issue Type].[CR])
)

Lauma / support@eazybi.com