Reporting planned hours on monthly bases

Hi there,
I am a new user of EasyBI in JIRA and would really appreciate some advice on creating issues report with calculated measure based on custom fields.
We have special issue type SOW defining work to be completed between SOWStartDate and SOWEndDate within estimated hours SOWTotalHours. All these are imported as Measures. What’s required is reporting average amount of SOWTotalHours on monthly bases (from current month on), ideally spreading the hours budget between SOWStartDate and SOWEndDate based on amount of work days. My idea is to define new calculated measure with avg hours per day and then aggregate it in report pairing with Time measure.
How would I define this new measure if it is possible at all in this scenario?
Thanks.

eazyBI by default sum any values per particular dates. You can get the Sum of SOWTotalHours with SOWStartDate or SOWTotalHours with SOWEndDate by default. You would need some calculation if you would like to split SOWTotalHours between SOWStartDate and SOWEndDate.

You can use the MDX formula. It might be more appropriate if this is for a one-time report only. This, however, might work slow. You can consider using the calculated JavaScript custom field for importing this as a new measure.

Here are two community posts with some example solutions in this:

The first one includes two options - using calculated MDX formula and JavaScript calculation based on default fields - issue created date, due date, and original estimates hours.

Another example uses the MDX formula with a custom Target start date and Target end date with Original estimated hours.

You can use any examples above and substitute the dates with your start date and end date. Please use measure SOWTotalHours crated instead of Original estimated hours.

Daina / support@eazybi.com

Many thanks Daina for very useful examples - exactly what was required to start off!
The MDX option seem to work just fine for me as we run that report couple times per month only.
Some tweaks were needed to be able to split the hours by extra dimensions, but it looks good now.

All the best for you and EazyBI team.
Paldies! :slight_smile:

Hi Daina.tupule.

Thanks for sharing the MDX code it is working for me. But the problem here is i am able to report the planned hours for the past dates from today. How to report the planned hours for the future dates. Can u please modify the MDX code

By default, eazyBI is adding members to Time dimension only if there is some activity on that date. Future dates often are not added as the only action that usually is in future is the Issue due dates or Sprint end date. In some cases, some past dates also could be missing due to the lack of activity per day. Nevertheless, if you would like to add projection in future or fill in gaps for past, you can add members to Time dimension manually.
See how to add members to Time dimension.

Daina /support@eazybi.com