Calculate days needed in the future

Kris Dewachter

Hi,

I'm trying to create a report that calculates the effort

This is what I have so far:

• I import a Google Sheet into eazybi that contains the available days on week level, into a measure called 'AvailableDays'.
• I created a calculated measure "Cumulative Available Days" that calculates the sum of the available days on a weekly level

Sum(
{ PreviousPeriods([Time].CurrentHierarchyMember),
[Time].CurrentHierarchyMember },
[Measures].[AvailableDays]
)

 

• In my report, I display the "Cumulative Days Spent" with this measure

CASE
WHEN DateCompare([Time].CurrentHierarchyMember.StartDate,Now())<=0
THEN
Sum(
{ PreviousPeriods([Time].CurrentHierarchyMember),
[Time].CurrentHierarchyMember },
[Measures].[Billed Days (from Tempo)]
)
END
)

 

• What I want to do now is to predict how much days I will spent in the future by 

Cumulative Days Spent + Cumulative Available Days = Days needed

I want the result to be displayed on a timeline of 2 months from today. I don't want the available days of the past to be included in the calculation.

 

How can I achieve this ?

Best regards,

Kris

 

 

 

 

Roberts Čāčus - eazyBI

Hi @Kris Dewachter

Do I understand you correctly - you want the cumulative sum of current and future periods? Unfortunately, eazyBI doesn't have an equivalent of the PreviousPeriods() function for future periods. But you can define a new calculated measure for "Cumulative Available Days" in periods positioned in the future compared to the current one:

Sum(
  Generate(
    Ascendants([Time].CurrentHierarchyMember),
    IIF(
      [Time].CurrentHierarchyMember IS [Time].CurrentHierarchyMember.LastSibling,
      {}, --empty set
      { 
        IIF(
        [Time].CurrentHierarchyMember.Level.Name = "Day",
        [Time].CurrentHierarchyMember,
        [Time].CurrentHierarchyMember.NextMember):[Time].CurrentHierarchyMember.LastSibling 
      } --set of fututer periods at day level
   )
 ),[Measures].[AvailableDays]
)

Then you can use the "Filter Time" option to display only Time dimension periods from today till 2 months from today with the filter "today and 2 months from now" - https://docs.eazybi.com/eazybi/analyze-and-visualize/date-filters.

 

Best,
Roberts // support@eazybi.com