How can I calculate the open issues (Not dynamic) before a date determined by the time filter?

Hello everyone! I need to be able to generate an initial stock of my requirements (issues). At the beginning of each month I want to see the photo of my initial stock with the number of open issues until the beginning of the month (In the Filter Dimension). This value should not move even if the issues open until the beginning of each month are closed. I would like to see this photo of my initial stock in a selected month and also in a timeline to know what stock I had month by month.

For example, in the attached photo (representative image) we have 286 open issues. If any of those open Issues is closed during the month, the value 286 should remain fixed because the resolved issue would be posted in the green column. The value 286 should represent all open issues up to the start of the current month. After the start of the current month if any of those 286 issues is closed the value should be maintained

I would also like to know how I can calculate the initial stock (blue line) month by month and see it reflected in a time line. In other words, the issues that were open until the month shown, even though they were closed later.

Thank you!

Hi @Emiliano_Romero,

If you want to see the number of issues open at the beginning of the month, you are actually looking for the number of open issues at the end of the previous month.
This way, you would freeze the reference to the specific date, and the changes during the current month are ignored.
You might create a calculated member in the Transition Status dimension to group the relevant Open statuses.
You might read about creating calculated members here - Calculated members in other dimensions.

If you have created a calculated member “Open statuses” within the Transition Status dimension and have months on report rows, you might use the following expression to see the Open issues at the beginning of the current month.

([Measures].[Issues history],
 [Transition Status].[Open statuses],
 [Time].CurrentHierarchy.CurrentMember.PrevMember)

Regards,
Oskars / support@eazyBI.com

1 Like