Query on "Time within version" Measure

Hi Team

I have a requirement to calculate the effort logged within a fixversion (PI) and I was using the below code. (I wanted to exclude the effort logged while the issue was being progressed in previous fix version (PI) and hence I am not using Hours Spent directly)

Sum(Filter(
Descendants([Time].CurrentHierarchyMember, [Time].[Day]),
[Measures].[Time within version] > 0
), [Measures].[Hours spent with sub-tasks])

This was working well, but now stopped working (May be due to upgrade to 6.5 ?). Can you suggest any alternate solutions if any

@gerda.zandersone

Hi @Shibu ,
Can you please share also your formula for “Time within version”? How do you calculate it? And if it has some reference to other not predefined eazyBI measures, share them as well.
The best for this situation would be to reach out to eazyBI support directly and provide us with your report definition: Export and import report definitions

Kind regards,
Gerda // support@eazyBI.com

Hi @gerda.zandersone

This is how we caliculate the “Time Within Version”

CASE WHEN
DateBetween([Time].CurrentHierarchyMember.StartDate,
[Measures].[Version start date],
[Measures].[Version release date]
)
THEN 1
END

Version start date and release date are properties and its definition below
CoalesceEmpty(
[Affects Version].CurrentHierarchyMember.get(‘Start date’),
[Fix Version].CurrentHierarchyMember.get(‘Start date’)
)

CoalesceEmpty(
[Affects Version].CurrentHierarchyMember.get(‘Release date’),
[Fix Version].CurrentHierarchyMember.get(‘Release date’)
)

Hi @Shibu
Can you share your report definition of where you use this formula?
I checked on my test environment that it works fine if the “Time” dimension together with the “Fix Version” or “Affects Version” is used thus my guess would be that the report context affects how your formula works.
Also, you can check if your “Version start date” and “Version release date” are not empty.

best,
Gerda

Hi @gerda.zandersone

Report definition below

{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “Effort spent on different issue types -NM2 - P\u0026L”,
“folder_name”: “New F”,
“result_view”: “pie_chart”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:[“[Measures].[Time spent within PI]”],“members”:[]}]},“rows”:{“dimensions”:[{“name”:“Issue Type”,“selected_set”:[“[Issue Type].[Story]”,“[Issue Type].[Bug]”,“[Issue Type].[Task]”],“members”:[],“bookmarked_members”:[{“depth”:1,“name”:“Story”,“full_name”:“[Issue Type].[Story]”},{“depth”:1,“name”:“Bug”,“full_name”:“[Issue Type].[Bug]”},{“depth”:1,“name”:“Task”,“full_name”:“[Issue Type].[Task]”}]}],“nonempty_crossjoin”:true},“pages”:{“dimensions”:[{“name”:“Fix Version”,“selected_set”:[“[Fix Version].[All Fix Versions]”,“[Fix Version].[TEST].[Unreleased].[PI 2022.1]”,“[Fix Version].[TEST].[Unreleased].[PI 2022.2]”],“members”:[{“depth”:0,“name”:“All Fix Versions”,“full_name”:“[Fix Version].[All Fix Versions]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false,“removed”:true},{“depth”:3,“name”:“PI 2022.2”,“full_name”:“[Fix Version].[TEST].[Unreleased].[PI 2022.2]”,“url_id”:76670,“parent_key”:“MPDI”}],“bookmarked_members”:[{“depth”:3,“name”:“PI 2022.1”,“full_name”:“[Fix Version].[TEST].[Unreleased].[PI 2022.1]”,“url_id”:77752,“parent_key”:“MPDI”},{“depth”:3,“name”:“PI 2022.2”,“full_name”:“[Fix Version].[TEST].[Unreleased].[PI 2022.2]”,“url_id”:76670,“parent_key”:“MPDI”}],“current_page_members”:[“[Fix Version].[TEST].[Unreleased].[PI 2022.2]”]},{“name”:“Assigned Team”,“selected_set”:[“[Assigned Team].[Retail P\u0026C - MO]”,“[Assigned Team].[Retail P\u0026C –NM2]”,“[Assigned Team].[PF]”,“[Assigned Team].[ER]”,“[Assigned Team].[Retail P\u0026C – MR]”],“members”:[{“depth”:1,“name”:“Retail P\u0026C – MR”,“full_name”:“[Assigned Team].[Retail P\u0026C – MR]”}],“bookmarked_members”:[{“depth”:1,“name”:“Retail P\u0026C - MO”,“full_name”:“[Assigned Team].[Retail P\u0026C - MO]”},{“depth”:1,“name”:“Retail P\u0026C –NM2”,“full_name”:“[Assigned Team].[Retail P\u0026C –NM2]”},{“depth”:1,“name”:“ER”,“full_name”:“[Assigned Team].[ER]”},{“depth”:1,“name”:“Retail P\u0026C – MR”,“full_name”:“[Assigned Team].[Retail P\u0026C – MR]”}],“current_page_members”:[“[Assigned Team].[Retail P\u0026C – MR]”]}]},“options”:{“nonempty”:true},“view”:{“current”:“pie_chart”,“maximized”:false,“pie_chart”:{“swap_axes”:false,“donut”:true,“show_legend”:false,“show_labels”:true,“data_labels”:“values_percentage”,“relative_size”:false,“series_options”:{}},“table”:{}},“calculated_members”:[],“description”:“Effort spent on different tickets \n====================\nEffort spent in hours for different issue types for the PI”}
} ],
“calculated_members”: [{“dimension”:“Measures”,“name”:“Hours spent with sub-tasks”,“format_string”:“#,##0.00”,“formula”:“[Measures].[Hours spent] +\n[Measures].[Sub-tasks hours spent]”},{“dimension”:“Measures”,“name”:“Version start date”,“format_string”:“mmm dd yyyy”,“formula”:“CoalesceEmpty(\n [Affects Version].CurrentHierarchyMember.get(‘Start date’),\n [Fix Version].CurrentHierarchyMember.get(‘Start date’)\n)”},{“dimension”:“Measures”,“name”:“Version release date”,“format_string”:“mmm dd yyyy”,“formula”:“CoalesceEmpty(\n [Affects Version].CurrentHierarchyMember.get(‘Release date’),\n [Fix Version].CurrentHierarchyMember.get(‘Release date’)\n)”},{“name”:“Time within version”,“dimension”:“Measures”,“formula”:“CASE WHEN\n DateBetween([Time].CurrentHierarchyMember.StartDate,\n [Measures].[Version start date],\n [Measures].[Version release date]\n )\nTHEN 1\nEND”,“format_string”:“#,##0”},{“name”:“Time spent within PI”,“dimension”:“Measures”,“formula”:“Sum(Filter(\n Descendants([Time].CurrentHierarchyMember, [Time].[Day]),\n [Measures].[Time within version] \u003e 0\n), [Measures].[Hours spent with sub-tasks])”,“format_string”:“”}]
}

Hi @gerda.zandersone can you pls check the above