Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

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 Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

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

Challenges
Coins

Gift kudos to your peers

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

Recognition
Ribbon

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!

Leaderboard

Calculate days needed in the future

Kris Dewachter
Community Leader
Community Leader
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

 

 

 

 

2 answers

1 accepted

0 votes
Answer 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

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.

 Screenshot 2023-11-13 at 13.54.55.png

Best,
Roberts // support@eazybi.com

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

Hi @Roberts Čāčus ,

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
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

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

Hi @Roberts Čāčus ,

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

This is what I have so far:

 

Screenshot 2023-11-22 at 15.07.00.png

  • 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

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

Hi @Roberts Čāčus ,

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

 

Screenshot 2023-11-24 at 16.14.14.png

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.

Screenshot 2023-11-24 at 16.17.26.png

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

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

Like Kris Dewachter likes this
Kris Dewachter
Community Leader
Community Leader
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

Like Roberts Čāčus likes this
0 votes
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

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.

image.png

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

Danut. 

  

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events