Calculate Number of Open Issues on First and Last Day of Month

Hello,
Looking to figure out how to create a report that can gracefully shift from month to month showing how many issues are Open on day 1 of a month, and how many are Open on the Last Day of a month.

I can fairly easily find open issues on a given day, but the problem is how do I make that report ‘move’ without having to specify specific dates every time I want to see a different month?

Any ideas are appreciated!

Hi,

If you have the month level members of the Time dimension in your report, the following expression gives the first day of the month which is present in your data:

[Time].CurrentHierarchyMember.FirstChild

Similarly, the following expression gives the last day of the month present in the data cube:

[Time].CurrentHierarchyMember.FirstChild.LastSibling

You can use those expressions in tuples to have the open issues on those dates.

Kindly,
Janis, eazyBI support

Thanks for the response!

I also got some help from Martins who helped me understand that the ‘Open Issues’ essentially already shows the amount of Open Issues on the last day of your given time period. Then he gave me the below to pull the first day of the time period:

(

[Measures].[Open issues],

[Time].Currentmember.firstchild.lag(1)

)

In any case, it’s working now!

1 Like