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 · Accepted solution]

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

[Kris Dewachter]

Hi @Roberts Čāčus - eazyBI ,

Thank you for your feedback.

I've been working with your suggestion for a few days, until i realized the report is not returning the results i need, and maybe i am making things too complicated.

Let me try to explain again.

I want to predict on what date a project might get finished. I have 2 measures for that.

1. The remaining estimates in days (Remaining estimated history)

2. The available days imported from a Google Sheet.

I would expect the output to look something like this:

Date (week)	Available Days from Google Sheet	Cumulative Available days	Remaining Days	Estimated Remaining Days (=Remaining Days - Cumulative Available Days)
03-jul-2023	5	5	21	16
10-jul-2023	5	10	21	11
17-jul-2023	4	14	21	7
24-jul-2023	7	21	21	0

I have a time filter on my report, so the timeline only shows dates => today.

Sounds simple enough. However, the problem I have is that EazyBI, for calculating the last column, also includes the 'Available Days' in the past (so defined in the Google Sheet before 03-jul-2023).

So I need EazyBI to only take into account the Available Days as of today's week, and not the past weeks.

I hope this makes sense.

Best regards,

Kris

[Roberts Čāčus - eazyBI]

Hi @Kris Dewachter

I might as well have misunderstood your requirement. If the previously suggested calculation doesn't fit your requirement, you can try the standard calculation "Cumulative sum" for the metric you want to consider for the periods visible in your report - https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports#Createreports-Addstandardcalculationsbasedonaselectedmeasure.

If that doesn't address your need, consider exporting and sharing the definition of the report - https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports/export-and-import-report-definitions.

Best,
Roberts // support@eazybi.com

[Kris Dewachter]

Hi @Roberts Čāčus - eazyBI ,

I tried the standard calculation, but something is not going correct.

This is what I have so far:

• Vandaag =  Current Date
• Remaining Estimated Days History (Remaining effort in Days)
• AvailableDays = Number of workingdays per week. This is imported from a Google Sheet
• Cumulative AvailableDays = Sum of AvailableDays
• Calculated Remaining work = Should be "Remaining Estimated Days History - Cumulative Available Days" but it's giving an incorrect result.

Report definition:

{
"cube_name": "Issues",
"cube_reports": [ {
"name": "Forecast When ready (Test Kris)",
"folder_name": "KPI",
"result_view": "table",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Today]","[Measures].[Remaining Estimated Days History ]","[Measures].[AvailableDays]","[Measures].[Cumulative AvailableDays]","[Measures].[Calculated Remaining Work]"],"members":[{"depth":0,"name":"Cumulative AvailableDays","full_name":"[Measures].[Cumulative AvailableDays]","drillable":false,"calculation_of":"[Measures].[AvailableDays]","calculation":"cumulative_sum_including_empty","format_string":"#,##0.00"}]}]},"rows":{"dimensions":[{"name":"Time","selected_set":["[Time.Weekly].[Week].Members"],"members":[],"bookmarked_members":[]}],"filter_by":{"conditions":[{"expression":"[Time.Weekly].CurrentHierarchyMember","operator":"\u003e=","value":"07-01-2023","value_type":"date"}]}},"pages":{"dimensions":[]},"options":{},"view":{"current":"table","maximized":false,"table":{}},"calculated_members":[]}
} ],
"calculated_members": [{"dimension":"Measures","name":"Remaining estimated hours history","format_string":"#,##0.00","formula":"Cache(\n NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n [Measures].[Remaining estimated hours change]\n ))\n + [Measures].[Remaining estimated hours change]\n)"},{"name":"Cumulative Available Days","dimension":"Measures","formula":"Sum(\n { PreviousPeriods([Time].CurrentHierarchyMember),\n [Time].CurrentHierarchyMember },\n [Measures].[AvailableDays]\n)","format_string":""},{"name":"Today","dimension":"Measures","formula":"IIF\n(DateInPeriod(Now(),\n[Time].CurrentHierarchyMember)\n,'Vandaag',null)","format_string":""},{"name":"Remaining Estimated Days History ","dimension":"Measures","formula":"[Measures].[Remaining estimated hours history] / 8","format_string":"#,##0.00"},{"name":"Calculated Remaining Work","dimension":"Measures","formula":"--annotations.group = Forecast When Ready\n[Measures].[Remaining Estimated Days History]- \n[Measures].[Cumulative Available Days]","format_string":"#,##0.00"}]
}

Best regards,

Kris

[Roberts Čāčus - eazyBI]

Hi @Kris Dewachter

The calculated measure "Calculated Remaining Work" returns unexpected results because of a typo.. either in the calculated measure "Remaining Estimated Days History " name - it has a trailing space; or in the formula of "Calculated Remaining Work", where "Remaining Estimated Days History" is referenced without the trailing space.

Please fix the typo and see whether you retrieve the expected values.

Best,
Roberts // support@eazybi.com

[Kris Dewachter]

Hi @Roberts Čāčus - eazyBI ,

I did not notice the space error. *embarrassed*. The result now looks much better. I get the correct calculations.

However, I wonder if it's possible to have the "Cumulative Available Days" start counting from today's date, and not take the historic days into account.

When I set a filter on date, "Cumulative Available Days" still uses all available days.

Thanks,

Kris

[Roberts Čāčus - eazyBI]

Hi @Kris Dewachter

No need to be embarrassed 😊 . With the Time dimension set to the "Weekly" hierarchy, try the calculated measure formula below for cumulative values for now and future periods:

CASE WHEN
  DateBeforePeriodEnd(
    Now(),
    [Time.Weekly].CurrentMember
  )
THEN
  Sum(
    {
      [Time.Weekly].[Week].CurrentDateMember:
      [Time.Weekly].CurrentMember
    },[Measures].[AvailableDays]
  )
END

This calculation will work for the "Weekly" hierarchy "Week" level members in the Time dimension.

Best,
Roberts // support@eazybi.com

[Kris Dewachter]

Thank you @Roberts Čāčus - eazyBI . This is exactly what i was looking for.

Best regards,

Kris

[Danut M _StonikByte_]

Hi @Kris Dewachter,

The calculation and the method used seem a bit "complicated" to me.  

Why not using a simpler method, like the Release Burndown Chart with Forecast gadget offered by our Great Gadgets app?

This can predict the ETA. It also works with time estimates.

The same Great Gadgets app offers also an Advanced Issue Filter Formula gadget that allows to do calculations based on the numerical fields from up to 6 filters. If your calculation is based on time estimates and time spent fields of Jira, you might be able achieve what you want.

I hope this helps. If you need help with these gadgets, please contact us at support@stonikbyte.com. 

Danut.