Hello Team,
In the commitment curve report we are trying to build in the Column Due Date testing we only need to show the number till current weekly on, but here in the report it is showing exceeding today’s date as in the below screenshot: I don’t want to show the rows highlighted
Measures used are as below:
- Due date testing :
Sum(
{ PreviousPeriods([Time].CurrentHierarchyMember),
[Time].CurrentHierarchyMember },
[Measures].[Due Date Missing]
)
Measure Due Date Missing is as below
Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
DateInPeriod(
[Measures].[Issue created date],
[Time].CurrenthierarchyMember
)
AND
IsEmpty([Issue].CurrentMember.Get('Due date'))
),
[Measures].[Issues created]
)
Report Definition:
{
"cube_name": "Issues",
"cube_reports": [ {
"name": "Build Deliverables Commitment Curve Release R2.0 \u0026 R2.5 – Overview (Test for Missing Due date)",
"folder_name": "Work In Progress",
"result_view": "table",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[In Review/PQM Check/Completed]","[Measures].[Baseline]","[Measures].[Revised Target]","[Measures].[Due date testing]"],"members":[{"depth":0,"full_name":"[Measures].[Due date testing]","format_string":"#,##0","report_specific":true,"name":"Due date testing","calculated":true}]}]},"rows":{"dimensions":[{"name":"Time","selected_set":["[Time.Weekly].[2023]","[Time.Weekly].[2024]","[Time.Weekly].[2025]"],"selected_set_expression":"DescendantsSet({{selected_set}}, [Time.Weekly].[Week])","members":[],"bookmarked_members":[]}],"filter_by":{"conditions":[{"expression":"[Time.Weekly].CurrentHierarchyMember","operator":"between","value":"Jun 03 2024,Feb 28 2025","value_type":"date"}],"others":false}},"pages":{"dimensions":[{"name":"Time","duplicate":true,"selected_set":["[Time.Weekly].[All Times]","[Time.Weekly].[Current week]","[Time.Weekly].[Previous week]","[Time.Weekly].[Last 4 weeks]","[Time.Weekly].[Last 12 weeks]"],"members":[{"depth":0,"name":"All Times","full_name":"[Time.Weekly].[All Times]","drillable":true,"type":"all","expanded":true,"drilled_into":false},{"depth":1,"name":"2023","full_name":"[Time.Weekly].[2023]","drillable":true,"parent_full_name":"[Time.Weekly].[All Times]"},{"depth":1,"name":"2024","full_name":"[Time.Weekly].[2024]","drillable":true,"parent_full_name":"[Time.Weekly].[All Times]"},{"depth":1,"name":"2025","full_name":"[Time.Weekly].[2025]","drillable":true,"parent_full_name":"[Time.Weekly].[All Times]"}],"bookmarked_members":[],"current_page_members":["[Time.Weekly].[2023]","[Time.Weekly].[2024]","[Time.Weekly].[2025]"]},{"name":"Status","selected_set":["[Status].[Status Work Item]","[Status].[Status Epic]"],"members":[{"depth":0,"name":"Status Work Item","full_name":"[Status].[Status Work Item]","calculated":true,"drillable":true,"dimension":"Status","expanded":false,"drilled_into":false}],"bookmarked_members":[],"current_page_members":["[Status].[Status Work Item]"]},{"name":"Issue Type","selected_set":["[Issue Type].[All Issue Types]"],"members":[{"depth":0,"name":"All Issue Types","full_name":"[Issue Type].[All Issue Types]","drillable":true,"type":"all","expanded":true,"drilled_into":false},{"depth":1,"name":"Work Item","full_name":"[Issue Type].[Work Item]","parent_full_name":"[Issue Type].[All Issue Types]"},{"depth":1,"name":"Work Item Dev","full_name":"[Issue Type].[Work Item Dev]","parent_full_name":"[Issue Type].[All Issue Types]"},{"depth":1,"name":"Work Item INT","full_name":"[Issue Type].[Work Item INT]","parent_full_name":"[Issue Type].[All Issue Types]"}],"bookmarked_members":[],"current_page_members":["[Issue Type].[Work Item]","[Issue Type].[Work Item Dev]","[Issue Type].[Work Item INT]"]},{"name":"Functional Domain","selected_set":["[Functional Domain].[LDC - Verticals]"],"members":[{"depth":0,"name":"LDC - Verticals","full_name":"[Functional Domain].[LDC - Verticals]","calculated":true,"drillable":true,"dimension":"Functional Domain","expanded":true,"drilled_into":false}],"bookmarked_members":[],"current_page_members":["[Functional Domain].[LDC - Verticals]"]},{"name":"Workstream","selected_set":["[Workstream].[All Workstreams]"],"members":[{"depth":0,"name":"All Workstreams","full_name":"[Workstream].[All Workstreams]","drillable":true,"type":"all","expanded":true,"drilled_into":false}],"bookmarked_members":[],"current_page_members":["[Workstream].[All Workstreams]"]},{"name":"Work Item type","selected_set":["[Work Item type].[Functional Specification]","[Work Item type].[Config Document]","[Work Item type].[OQS (Test Script)]","[Work Item type].[Total Developments ]","[Work Item type].[Integration Pattern]","[Work Item type].[Configuration Activities ]","[Work Item type].[All Work Item types]"],"members":[{"depth":0,"name":"All Work Item types","full_name":"[Work Item type].[All Work Item types]","drillable":true,"type":"all"}],"bookmarked_members":[{"depth":1,"name":"OQS","full_name":"[Work Item type].[OQS]"}],"current_page_members":["[Work Item type].[All Work Item types]"]},{"name":"Issue Subtype","selected_set":["[Issue Subtype].[Total Work Items]","[Issue Subtype].[S/4HANA Total]","[Issue Subtype].[S/4HANA Eco System]","[Issue Subtype].[S/4HANA Report]","[Issue Subtype].[S/4HANA Report - EA]","[Issue Subtype].[EDW Report]","[Issue Subtype].[Regression]","[Issue Subtype].[EDW PaPM]","[Issue Subtype].[EDW Rollout]","[Issue Subtype].[All Issue Subtypes]"],"members":[{"depth":0,"name":"All Issue Subtypes","full_name":"[Issue Subtype].[All Issue Subtypes]","drillable":true,"type":"all"}],"bookmarked_members":[],"current_page_members":["[Issue Subtype].[All Issue Subtypes]"]},{"name":"PtQ Target","selected_set":["[PtQ Target].[All PtQ Targets]"],"members":[{"depth":0,"name":"All PtQ Targets","full_name":"[PtQ Target].[All PtQ Targets]","drillable":true,"type":"all","expanded":true,"drilled_into":false}],"bookmarked_members":[],"current_page_members":["[PtQ Target].[All PtQ Targets]"]},{"name":"Delivery Model","selected_set":["[Delivery Model].[All Delivery Models]"],"members":[{"depth":0,"name":"All Delivery Models","full_name":"[Delivery Model].[All Delivery Models]","drillable":true,"type":"all","expanded":true,"drilled_into":false}],"bookmarked_members":[],"current_page_members":["[Delivery Model].[All Delivery Models]"]},{"name":"Defect Indicator","selected_set":["[Defect Indicator].[All Defect Indicators]"],"members":[{"depth":0,"name":"All Defect Indicators","full_name":"[Defect Indicator].[All Defect Indicators]","drillable":true,"type":"all"}],"bookmarked_members":[],"current_page_members":["[Defect Indicator].[All Defect Indicators]"]},{"name":"Complexity","selected_set":["[Complexity].[All Complexities]"],"members":[{"depth":0,"name":"All Complexities","full_name":"[Complexity].[All Complexities]","drillable":true,"type":"all","expanded":true,"drilled_into":false}],"bookmarked_members":[],"current_page_members":["[Complexity].[All Complexities]"]},{"name":"Release_Version","selected_set":["[Release_Version].[All Release_Versions]"],"members":[{"depth":0,"name":"All Release_Versions","full_name":"[Release_Version].[All Release_Versions]","drillable":true,"type":"all","expanded":true,"drilled_into":false},{"depth":1,"name":"R2.0","full_name":"[Release_Version].[R2.0]","parent_full_name":"[Release_Version].[All Release_Versions]"}],"bookmarked_members":[],"current_page_members":["[Release_Version].[R2.0]"]},{"name":"Has Due Date","selected_set":["[Has Due Date].[All Has Due Dates]"],"members":[{"depth":0,"name":"All Has Due Dates","full_name":"[Has Due Date].[All Has Due Dates]","drillable":true,"type":"all"}],"bookmarked_members":[],"current_page_members":["[Has Due Date].[All Has Due Dates]"]}]},"options":{},"view":{"current":"table","maximized":false,"table":{"freeze_header":false}},"calculated_members":[{"dimension":"Measures","name":"Backlog New","formula":"IIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[Backlog]),NULL)","format_string":""},{"dimension":"Measures","name":"In Review / Completed","formula":"IIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[In Review]),NULL)+\nIIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[Completed]),NULL)","format_string":""},{"dimension":"Measures","name":"Due date testing","formula":"Sum(\n { PreviousPeriods([Time].CurrentHierarchyMember),\n [Time].CurrentHierarchyMember },\n [Measures].[Due Date Missing]\n)","format_string":"#,##0"}]}
} ],
"calculated_members": [{"dimension":"Measures","name":"Issues history","format_string":"#,##0","formula":"Cache(\n NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n [Measures].[Transitions to]\n - [Measures].[Transitions from]\n ))\n + [Measures].[Transitions to]\n - [Measures].[Transitions from]\n)"},{"dimension":"Measures","name":"Issue created date","format_string":"mmm dd yyyy","formula":"[Issue].CurrentHierarchyMember.get('Created at')"},{"dimension":"Time","name":"Current week","format_string":"","formula":"Aggregate({\n [Time.Weekly].[Week].CurrentDateMember\n})","dimension_hierarchy":"Weekly"},{"dimension":"Time","name":"Previous week","format_string":"","formula":"Aggregate({\n [Time.Weekly].[Week].CurrentDateMember.PrevMember\n})","dimension_hierarchy":"Weekly"},{"dimension":"Time","name":"Last 4 weeks","format_string":"","formula":"Aggregate(\n [Time.Weekly].[Week].DateMembersBetween('4 weeks ago', 'today')\n)","dimension_hierarchy":"Weekly"},{"dimension":"Time","name":"Last 12 weeks","format_string":"","formula":"Aggregate(\n [Time.Weekly].[Week].DateMembersBetween('12 weeks ago', 'today')\n)","dimension_hierarchy":"Weekly"},{"name":"Status Work Item","dimension":"Status","formula":"Aggregate({\n [Status].[Backlog],\n [Status].[Scheduled],\n [Status].[In Progress],\n [Status].[Screening],\n [Status].[In Screening],\n [Status].[In Review],\n [Status].[Testing],\n [Status].[PQM Check],\n [Status].[TD Check],\n [Status].[Completed],\n [Status].[Done],\n [Status].[On Hold]\n})","format_string":""},{"name":"Baseline","dimension":"Measures","formula":"/*cumulativeSum([Measures].[Issues with Target end])*/\n \nSum(\n { PreviousPeriods([Time].CurrentHierarchyMember),\n [Time].CurrentHierarchyMember },\n [Measures].[Issues with Target end]\n)","format_string":""},{"name":"Revised Target","dimension":"Measures","formula":"/*CumulativeSum([Measures].[Issues with due date])*/\n\nSum(\n { PreviousPeriods([Time].CurrentHierarchyMember),\n [Time].CurrentHierarchyMember },\n [Measures].[Issues with due date]\n)","format_string":""},{"name":"In Review/PQM Check/Completed","dimension":"Measures","formula":"IIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[In Review]),NULL)+\nIIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[PQM Check]),NULL)+\nIIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[Completed]),NULL)+\nIIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[Done]),NULL)","format_string":""},{"name":"Functional Specification","dimension":"Work Item type","formula":"[Work Item type].[Design]","format_string":""},{"name":"LDC - Verticals","dimension":"Functional Domain","formula":"Aggregate({\n[Functional Domain].[Commercial],\n[Functional Domain].[Finance \u0026 Procurement],\n[Functional Domain].[Operations ],\n[Functional Domain].[Solution],\n[Functional Domain].[Data],\n[Functional Domain].[Reporting \u0026 Analytics],\n[Functional Domain].[Data Migration],\n[Functional Domain].[Adopt],\n[Functional Domain].[Unmatch],\n[Functional Domain].[(none)]\n})","format_string":""},{"name":"S/4HANA Eco System","dimension":"Issue Subtype","formula":"Aggregate(\n\t{\n\t\t[Issue Subtype].[(none)],\n\t\t[Issue Subtype].[Integration-Topic],\n\t\t[Issue Subtype].[Business],\n\t\t[Issue Subtype].[Change-Impact],\n\t\t[Issue Subtype].[Data],\n [Issue Subtype].[Data Quality],\n [Issue Subtype].[Dependent Initiative],\n [Issue Subtype].[ERC],\n [Issue Subtype].[GAT],\n [Issue Subtype].[ISRM],\n [Issue Subtype].[LAT Query],\n [Issue Subtype].[Master Data],\n [Issue Subtype].[OQT],\n [Issue Subtype].[Others],\n [Issue Subtype].[PQT],\n [Issue Subtype].[S\u0026A],\n [Issue Subtype].[Transactional Data],\n [Issue Subtype].[FCC]\n})","format_string":""},{"name":"EDW Report","dimension":"Issue Subtype","formula":"[Issue Subtype].[R\u0026A_EDW]","format_string":""},{"name":"Regression","dimension":"Issue Subtype","formula":"[Issue Subtype].[R\u0026A_Regression]","format_string":""},{"name":"S/4HANA Report","dimension":"Issue Subtype","formula":"/*[Measures].[S/4HANA Report]*/\n/*[Issue Subtype].[R\u0026A]-[Label].[Exclude EDW \u0026 Regression \u0026 EA]*/\n\n[Issue Subtype].[R\u0026A_ABAP]","format_string":""},{"name":"S/4HANA Report","dimension":"Measures","formula":"[Issue Subtype].[R\u0026A]-\n([Label].[R\u0026A_EDW]-[Label].[R\u0026A_Regression])","format_string":""},{"name":"Total Work Items","dimension":"Issue Subtype","formula":"Aggregate(\n\t{\n\t\t[Issue Subtype].[S/4HANA Eco System],\n\t\t[Issue Subtype].[EDW Report],\n\t\t[Issue Subtype].[Regression],\n\t\t[Issue Subtype].[S/4HANA Report],\n\t\t[Issue Subtype].[S/4HANA Report - EA],\n\t\t[Issue Subtype].[EDW PaPM],\n [Issue Subtype].[R\u0026A],\n [Issue Subtype].[EDW Rollout],\n [Issue Subtype].[GRC]\n})\n","format_string":""},{"name":"S/4HANA Report - EA","dimension":"Issue Subtype","formula":"/*[Label].[EA]*/\n\n[Issue Subtype].[R\u0026A_EA]","format_string":""},{"name":"S/4HANA Total","dimension":"Issue Subtype","formula":"Aggregate(\n\t{\n\t\t[Issue Subtype].[S/4HANA Report],\n [Issue Subtype].[S/4HANA Report - EA],\n [Issue Subtype].[S/4HANA Eco System]\n})","format_string":""},{"name":"EDW PaPM","dimension":"Issue Subtype","formula":"[Issue Subtype].[R\u0026A_PaPM]","format_string":""},{"name":"Operations ","dimension":"Functional Domain","formula":"Aggregate({\n[Functional Domain].[Manufacturing],\n[Functional Domain].[Operations],\n[Functional Domain].[QA],\n[Functional Domain].[TRD],\n[Functional Domain].[SCM],\n[Functional Domain].[PLM]\n})","format_string":""},{"name":"OQS (Test Script)","dimension":"Work Item type","formula":"[Work Item type].[OQS]","format_string":""},{"name":"Config Document","dimension":"Work Item type","formula":"[Work Item type].[CSD]","format_string":"#,##0"},{"name":"Development Factory","dimension":"Work Item type","formula":"[Work Item type].[Development]","format_string":""},{"name":"Other Core Developments","dimension":"Work Item type","formula":"[Work Item Type].[Dev Other]","format_string":""},{"name":"Development EDW","dimension":"Work Item type","formula":"[Work Item type].[Dev EDW]","format_string":""},{"name":"Development Integration","dimension":"Work Item type","formula":"[Work Item type].[Dev Int]","format_string":""},{"name":"Total Developments ","dimension":"Work Item type","formula":"Aggregate({\n[Work Item type].[Development Factory],\n[Work Item type].[Development EDW],\n[Work Item type].[Development Integration],\n[Work Item type].[Other Core Developments]\n})","format_string":""},{"name":"Integration Pattern","dimension":"Work Item type","formula":"[Work Item Type].[Interface]","format_string":""},{"name":"Status Epic","dimension":"Status","formula":"Aggregate({\n [Status].[Backlog],\n [Status].[Ready for Handover],\n [Status].[Scheduled],\n [Status].[In Design],\n [Status].[In Progress],\n [Status].[In Review],\n [Status].[Ready For Approval],\n [Status].[Ready for PtQ],\n [Status].[Testing],\n [Status].[Ready for PTP],\n [Status].[On Hold],\n [Status].[Completed]\n})","format_string":""},{"name":"Configuration Activities ","dimension":"Work Item type","formula":"[Work Item type].[Config]","format_string":""},{"name":"EDW Rollout","dimension":"Issue Subtype","formula":"[Issue Subtype].[R\u0026A_Rollout]","format_string":""},{"name":"Due Date Missing","dimension":"Measures","formula":"Sum(\nFilter(\nDescendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),\nDateInPeriod(\n[Measures].[Issue created date],\n[Time].CurrenthierarchyMember\n)\nAND\nIsEmpty([Issue].CurrentMember.Get('Due date'))\n),\n[Measures].[Issues created]\n)","format_string":"#,##0"}]
}
Please let me know how can we achieve that