i solved this problem, following these steps
1. i added Sprint in pages and Time dimension in rows the time with hierarchy level as day
2. i created a measure called working days to remove saturday and sundays added the measure and filtered >0 i made the same thing with the time within sprint that already comes in eazybi by default and i removed both coluns
[Measures].[ Working days ] =
DateDiffWorkDays(
[Time].CurrentHierarchyMember.StartDate,
[Time].CurrentHierarchyMember.NextStartDate
)
3. to create the projection without real expense i created three measure the first measure i called by total hours sum v3
the code is below
[Measures].[ total hours sum v3] =
Sum(
{PreviousPeriods([Time].CurrentHierarchyMember),
[Time].CurrentHierarchyMember}
,
[Measures].[Original estimated hours]
)
Then i created the second measure called start value v3, for this i used de following code , i used the item 9 because my squads usually add more hours during the sprint and this value change, só he takes the final value
[Measures].[start value v3] =
(VisibleRowsSet().Item(9).Item(0),
[Measures].[total hours sum v3]
)
And finally i created the measure projeção that means projection in english, using the other measures that i created following the code bellow
-- annotations.group = Time tracking
CASE WHEN
DateBetween([Time].CurrentHierarchyMember.StartDate,
DateWithoutTime([Sprint].CurrentMember.get('Start date')),
[Sprint].CurrentMember.get('End date')
)
OR
DateInPeriod(
[Sprint].CurrentMember.get('Start date'),
[Time].CurrentHierarchyMember
)
THEN
([Measures].[start value v3],
[Time].CurrentHierarchy.DefaultMember) *
(
DateDiffWorkdays(
DateWithoutTime([Sprint].CurrentMember.get('Start date')),
DateWithoutTime([Sprint].CurrentMember.get('End date'))
) -
DateDiffWorkdays(
DateWithoutTime([Sprint].CurrentMember.get('Start date')),
[Time].CurrentHierarchyMember.StartDate
)
) /
DateDiffWorkdays(
DateWithoutTime([Sprint].CurrentMember.get('Start date')),
DateWithoutTime([Sprint].CurrentMember.get('End date'))
)
END
5. to create the real expenses i too used star value v3 measure and but i too used a measure called Horas gasts that means hours spent the horas gastas measure code bellow
[Measures].[ Horas gastas] =
([Issue Type].[Sub-task],
[Measures].[Hours spent])
and then i finally create the real expense(gastos reais) measure following the code below
(VisibleRowsSet().Item(0).Item(0),
[Measures].[start value v3]
)
-
Sum(
{
Filter(
[Time].CurrentHierarchyMember.level.Members,
Rank(
[Time].CurrentHierarchyMember,
VisibleRowsSet()
) = 1
).item(0):
[Time].CurrentHierarchyMember},
[Measures].[Horas gastas]
)
i noticed a differençe of 1,3 between it’s happening is that because i started the sprint near the end of the day but the value is correct.
final result