Hello,
I have issues whith a start and finish date (custom fields). In these issues there is a field “Effort planned” in where the hours planned for the complete time period (start to finish) is written. I want to create a report based on months where I can sum the effort but only for the issues where the start date is in the time period. If the end date is outside the period shown, then I need to calculate only the effort for the days in the time period shown.
I already created a measure which calculates the effort / day so I can multiply the effort per day with the days actually shown.
I cannot figure out, what formula in mdx to use to sum this effort / day up to show the sum for all issues in the shown time period.
Your help is much appreciated.
Thanks a lot,
Michael
I could figure out the formula, I had to take all dates and date range combinations into account to get the right sum formula.
Hi @maydemir
I am glad to hear you managed to get to the solution by yourself. I encourage you to share the solution you came up with the other members of the community.
Alternatively, I can share an example with a similar requirement - get the number of original estimated hours divided by the number of workdays between two dates:
Sum(
--get set of issues that have the 'Target start Date' and/or 'Target end Date' in the time period currently selected
Filter(
Descendants(
[Issue].CurrentHierarchyMember,[Issue].[Issue]
),
DateBeforePeriodEnd(
[Measures].[Issue Target start],
[Time].CurrentHierarchyMember
)
AND
DateAfterPeriodEnd(
[Measures].[Issue Target end],
[Time].CurrentHierarchyMember.PrevMember
)
--exclude weekends
AND DateDiffWorkDays(
[Time].CurrentHierarchyMember.StartDate,
[Time].CurrentHierarchyMember.NextStartDate
) > 0
),
--get the number of original estimated hours for those issues
CASE WHEN
[Time].CurrentHierarchy.Level.Name <> 'Day'
AND
Cache(DateDiffWorkDays(
[Measures].[Issue Target start],
DateAddDays([Measures].[Issue Target end],1)
)) > 0
THEN
(
([Measures].[Original estimated hours],[Time].CurrentHierarchy.DefaultMember)
/
--divide that number with the working days that issue will spend between the 'Target start date' and 'Target end date'. Multiply by dates the issue will spend in the current Time hierarchy member.
DateDiffWorkDays(
[Measures].[Issue Target start],
DateAddDays([Measures].[Issue Target end],1)
)
*
--multiply by the number of workdays the issues are active
(DateDiffWorkdays(
TimestampToDate(MAX({
[Measures].[Target Start TimeStamp],
[Measures].[Time Start TimeStamp]
}, [Measures].CurrentMember
))
,
TimestampToDate(MIN({
[Measures].[Target End TimeStamp],
[Measures].[Time End TimeStamp]
}, [Measures].CurrentMember
))
)))
WHEN
[Time].CurrentHierarchy.Level.Name = 'Day'
AND
DateDiffWorkDays(
[Measures].[Issue Target start],
DateAddDays([Measures].[Issue Target end],1)
) > 0
THEN
([Measures].[Original estimated hours],[Time].CurrentHierarchy.DefaultMember)
/
DateDiffWorkDays(
[Measures].[Issue Target start],
DateAddDays([Measures].[Issue Target end],1)
)
END
)
The measures “Target Start TimeStamp” and similar ones used in determining number of workdays the issues were active are calculated measures with the DateToTimestamp() function - DateToTimestamp([Measures].[Issue Target start]).
The report then can look similar to the one below:
Best,
Roberts // eazyBI support
Hi @roberts.cacus,
thanks for sharing the example, I’m trying to do a similar report and it’s very useful.
I have a quick question, how’ the Time End Time stamp is calculated ?
As you can see from the screenshot below, in my report the daily values are correct but up monthly ones are not:
Thanks,
Claudio
Hi @klawde,
The calculated measure “Time End TimeStamp” gets the time stamp of the next Time dimension period start date:
DateToTimeStamp([Time].CurrentHierarchyMember.NextStartDate)
Best,
Roberts // support@eazybi.com
@roberts.cacus I would like to do this exact thing but instead of by hours at this moment I want to do just an issue count. do you know how that would be possible?
Hi @Jaylyn_Brown,
You can try to replace the measure “Original estimated hours” in the formula with the measure “Issues created”. That could give you a number of issues.
Best,
Roberts // support@eazybi.com