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