We have the Expected Team Size during the period of the project in an Excel file used as a source of EazyBI.
I’ve created the Measure “Future Team Size” to get only the expected team size in the future :

CASE WHEN
DateCompare(
now(),
[Time].CurrentHierarchyMember.StartDate
) < 0
THEN
([Measures].[Expected Team Size])
END

Now , at a given date in the future ( the new end date of the project) , I would like to sum the total remaining workload, i.e the sum of “Future Team Size” until the new end date.

In order to do that I tried this measure ( let’s say the new end date is 02/01/25 ):

CASE WHEN
DateInPeriod( DateParse("2 january 2025"),
[Time].CurrentHierarchyMember)
THEN
(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
[Measures].[Future Team Size] ))
+
[Measures].[Future Team Size]
END

As a result we have only the Future Team Size value at “2 january 2025” => the sum return EMPTY.

If I replace Future Team Size by Expected Team Size in the last calculated measure, the Sum is working well ( but it not the Sum I want)
So it seems that the problem is coming from Future Team Size , but I dont understand why.

I found something else : in my example the team size is 20 from today ( 10/10/24) to the end of the project ( 02/01/25).
The result of the formula below is 20 instead of 240 expected

CASE WHEN
DateInPeriod( DateParse("2 january 2025"),
[Time].CurrentHierarchyMember)
THEN
(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
[Measures].[Future Team Size] ))
+
[Measures].[Future Team Size]
END

But if I change the end date to any date in 2024 , the result is correct.
For instance , the result of the formula below is 160 as expected :

CASE WHEN
DateInPeriod( DateParse("2 dec 2024"),
[Time].CurrentHierarchyMember)
THEN
(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
[Measures].[Future Team Size] ))
+
[Measures].[Future Team Size]
END

Hi,
Welcome to the eazyBI community.
First, ir would be great to know how the Expected team size is mapped against the Time in the data mapping. Also, what are the values, to what periods they are linked, how do they change etc.
I presume it is mapped to weeks and you expect value for each future week. That works fine within a year, where previous periods return full weeks between now and the final date.
Still, that runs into trouble when PreviousPeriods() for 02 Jan 2025 returns Time dimension members like:

previous years (OK to be zero),

year 2024 which is now zero as its StartDate is less than now,
and

01 Jan 2025, which has the start date in the future (and delivers value).

Another approach is to use the actual period with some of DateDiff…Days() functions.
You might read more about them here -
DateDiffDays
DateDiffWorkdays

If the “Expected Team Size” is changing over periods - you might use the DateMembersBetween() function to create a flat set of weeks/days between two dates. Then, sum up values for Future team size executed for each of these members separately.
The expression might then be as follows.

Sum(
--set of time members
[Time.Weekly].[Week].DateMembersBetween(
--start date
"now",
--final date
"02 Jan 2025"),
--numeric value
[Measures].[Future Team Size]
)

Still, the solution depends on your setup and data mappings details.
Regards,
Oskars / support@eazyBI.com