cancel
Showing results for
Did you mean:
See all
##### Top groups
Explore all groups

## Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user

Level 1: Seed

25 / 150 points

Next: Root

## Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

What goes around comes around! Share the love by gifting kudos to your peers.

## Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

# Calculate days needed in the future

Kris Dewachter
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Nov 10, 2023

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

#### 1 accepted

Roberts Čāčus
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Nov 13, 2023

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
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Nov 20, 2023

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Nov 21, 2023

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
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Nov 22, 2023

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Nov 24, 2023

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
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Nov 24, 2023

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Nov 29, 2023

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 likes this
Kris Dewachter
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Nov 29, 2023

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

Best regards,

Kris

Roberts Čāčus likes this
Danut M [StonikByte]
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Nov 10, 2023 • edited Nov 20, 2023

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.