COST using FTE approach

DKostiuk · November 24, 2023, 11:02am

Hello,

I am trying to create the Investment measurement report (plan and fact) for Initiatives.

My approach is the following:

Use FTE calculation for the factual result
A FTE would be considered working on a project if they are assigned to the issue in a month.
A FTE is considered to be working 100% of the month. So if they worked on one issue all month, that issue would receive 1 FTE for its aggerate investment measurement. If they worked on multiple issues their allocation would be split based on the time they were assigned on each issue.

step 1. I did it using “Workdays assigned”

step 2. I think I can do it with additional import of the table from step 1. How can I create it in another way

For the example, we could assume 1 FTE equates to 100 money per month.

step 3. Target table. Please, help me understand How to do it, or only additional import helps me.

Time - hierarchy All, Year, Quarter, Month (default)
Issue - hierarchy (created) - Initiative - Epic

Thank you ahead
Best regards,
Dmytro Kostiuk

Elita.Kalane · November 29, 2023, 12:36pm

Hi @DKostiuk
Thanks for posting your question!
If I understood your use case, the solution in the screenshot below is what you are looking for.
The particular issue was assigned to Adam Mint on June 13, and it was then assigned to Anna Linda on Jul 12 (almost 29 days for Adam Mint). You can see in the screenshot below that the formula splits the assigned days for Adam Mint and Anna Linda accordingly.

Here is the formula to use:

CASE
WHEN
DateInPeriod(
TimestampToDate(([Measures].[Transition to first timestamp],
[Transition Field].[Assignee])),
[Time].CurrentHierarchyMember)
THEN
DateDiffDays(
TimestampToDate(([Measures].[Transition to first timestamp],
[Transition Field].[Assignee])),
[Time].CurrentHierarchyMember.NextStartDate
)
WHEN
DateInPeriod(
TimestampToDate(([Measures].[Transition from first timestamp],
[Transition Field].[Assignee])),
[Time].CurrentHierarchyMember)
THEN
DateDiffDays(
[Time].CurrentHierarchyMember.StartDate,
TimestampToDate(([Measures].[Transition from first timestamp],
[Transition Field].[Assignee]))
)
WHEN
IsEmpty([Measures].[Transitions to assignee]) AND
IsEmpty([Measures].[Transitions from assignee]) AND
[Measures].[Issues history] >0
THEN
DateDiffDays(
[Time].CurrentHierarchyMember.StartDate,
[Time].CurrentHierarchyMember.NextStartDate
)
END

Best wishes,
Elita from support@eazybi.com

DKostiuk · December 5, 2023, 2:23pm

Hello, Elita!

Sorry, you show the table for step 2 (not the requested table in step 3)

please, can you explain the new measure “Days assigned”.
from EazyBI documentation - Import issue change history
Counts days for each assignee change of the issue. The measure also counts days till resolution day for the assignee who resolved the issue. Use this measure with the Assignee dimension.

In the table, we can see the number of days is more than days in month. It is possible because one assignee can be assigned for several issues.

When I select “drill through issue” I see the number for one issue and it is more than days in a month.
please, can you explain How it is possible?

Best regards,
DKostiuk