In the attached screenshot considering custom fields as Status and Due date
In the measures Delay - Com, I wanted to show only issues overdue on that particular day, but I guess the existing formula is not working as expected.
Here I want to show overdue items considering US_COM - Completion Due Date (F2C) as due date, which are not moved to Country Review US Com field value not in Accepted Used As Is and Accepted with change.
Highlighted values are considered as done.
Please help me to build the report with providing the measure for the same.
Report Definition:
{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “US_Com-Due Date Outlook”,
“folder_name”: “LDC - BPM”,
“result_view”: “bar_chart”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:[“[Measures].[US_COM - To-Do]”,“[Measures].[Delay - COM]”],“members”:[{“depth”:0,“full_name”:“[Measures].[US_COM - To-Do]”,“format_string”:“#,##0”,“report_specific”:true,“name”:“US_COM - To-Do”,“calculated”:true,“id”:“[Measures].[US_COM - To-Do]”},{“depth”:0,“full_name”:“[Measures].[Delay - COM]”,“format_string”:“#,##0”,“report_specific”:true,“name”:“Delay - COM”,“calculated”:true,“id”:“[Measures].[Delay - COM]”}]}]},“rows”:{“dimensions”:[{“name”:“Time”,“selected_set”:[“[Time.Weekly].[All Times]”],“selected_set_expression”:“DescendantsSet({{selected_set}}, [Time.Weekly].[Day])”,“members”:,“bookmarked_members”:}],“filter_by”:{“conditions”:[{“expression”:“[Time.Weekly].CurrentHierarchyMember”,“operator”:“between”,“value”:“Dec 01 2023, May 31 2024”,“value_type”:“date”}],“others”:false}},“pages”:{“dimensions”:[{“name”:“Status”,“selected_set”:[“[Status].[All but cancelled / rejected]”],“members”:[{“depth”:0,“name”:“All but cancelled / rejected”,“full_name”:“[Status].[All but cancelled / rejected]”,“calculated”:true,“drillable”:true}],“bookmarked_members”:,“current_page_members”:[“[Status].[All but cancelled / rejected]”]},{“name”:“Country Review US Com”,“selected_set”:[“[Country Review US Com].[US_Com]”],“members”:[{“depth”:0,“name”:“US_Com”,“full_name”:“[Country Review US Com].[US_Com]”,“calculated”:true,“drillable”:true}],“bookmarked_members”:,“current_page_members”:[“[Country Review US Com].[US_Com]”]},{“name”:“Epic Type”,“selected_set”:[“[Epic Type].[Process L4]”],“members”:[{“depth”:1,“name”:“Process L4”,“full_name”:“[Epic Type].[Process L4]”}],“bookmarked_members”:[{“depth”:1,“name”:“Process L4”,“full_name”:“[Epic Type].[Process L4]”}],“current_page_members”:[“[Epic Type].[Process L4]”]},{“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,“expanded”:true,“drilled_into”:false},{“depth”:0,“name”:“Finance \u0026 Procurement”,“full_name”:“[Functional Domain].[Finance \u0026 Procurement]”,“calculated”:true,“drillable”:true,“expanded”:true,“drilled_into”:false,“parent_full_name”:“[Functional Domain].[LDC - Verticals]”}],“bookmarked_members”:,“current_page_members”:[“[Functional Domain].[LDC - Verticals]”]},{“name”:“Workstream”,“selected_set”:[“[Workstream].[LDC - Workstreams]”],“members”:[{“depth”:0,“name”:“LDC - Workstreams”,“full_name”:“[Workstream].[LDC - Workstreams]”,“calculated”:true,“drillable”:true,“dimension”:“Workstream”}],“bookmarked_members”:,“current_page_members”:[“[Workstream].[LDC - Workstreams]”]},{“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]”,“[Time.Weekly].[Two weeks ago]”],“members”:[{“depth”:0,“name”:“All Times”,“full_name”:“[Time.Weekly].[All Times]”,“drillable”:true,“type”:“all”}],“bookmarked_members”:,“current_page_members”:[“[Time.Weekly].[All Times]”]}]},“options”:{“nonempty”:true},“view”:{“current”:“bar_chart”,“maximized”:false,“bar_chart”:{“stacked”:false,“vertical”:true,“swap_axes”:false,“data_labels”:true,“series_options”:{“Cumulative Issues created”:{“type”:“column”},“Cumulative Accepted - Use As Is “:{“type”:“column”},“Cumulative Accepted - Use with Change”:{“type”:“column”},“Scope”:{“type”:“line”},”$total”:{“type”:“line”,“dataLabelType”:“top horizontal”},“DE Accepted - Use As Is”:{“color”:“#006644”,“dataLabelType”:“top horizontal”},“DE Accepted - Use with Change”:{“color”:“#34AD70”,“dataLabelType”:“middle horizontal”},“DE In Progress”:{“color”:“#FFA806”,“dataLabelType”:“middle horizontal”},“Status missing”:{“color”:“#930F34”,“dataLabelType”:“middle horizontal”},“DE Planned”:{“color”:“#274380”,“dataLabelType”:“middle horizontal”},“DE - Completion Due Date”:{“type”:“column”,“dataLabelType”:“top horizontal”,“symbol”:“square”,“color”:“#F24B13”},“DE - Scope”:{“type”:“line”,“dataLabelType”:“top horizontal”,“symbol”:false},“DE - To Do”:{“color”:“#F24B13”,“dataLabelType”:“top horizontal”},“Delay”:{“dataLabelType”:“top horizontal”},“US - To Do”:{“color”:“#F95E16”,“dataLabelType”:“top horizontal”},“Delay - COM”:{“dataLabelType”:“top horizontal”},“Delay - CGT”:{“dataLabelType”:“top horizontal”},“DELAY - GTX”:{“dataLabelType”:“top horizontal”},“US_COM - To-Do”:{“dataLabelType”:“top horizontal”}},“x_axis”:{“0”:{“title”:“US_COM - Weekly Progress”}},“font_size”:“large”,“show_full_caption”:[“Measures”]},“table”:{“freeze_header”:true}},“calculated_members”:[{“dimension”:“Measures”,“name”:“Accepted - Use As Is OLD”,“formula”:“[Country Review DE].[Accepted - Use As Is]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Accepted - Use with Change - OLD”,“formula”:“[Country Review DE].[Accepted - Use with Change]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“DE Accepted - Use As Is”,“formula”:“IIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Country Review DE].[Accepted - Use As Is]),NULL)”,“format_string”:“”},{“dimension”:“Measures”,“name”:“DE Accepted - Use with Change”,“formula”:“IIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Country Review DE].[Accepted - Use with Change]),NULL)”,“format_string”:“”},{“dimension”:“Measures”,“name”:“DE Planned”,“formula”:“IIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Country Review DE].[Planned]),NULL)”,“format_string”:“”},{“dimension”:“Measures”,“name”:“DE In Progress”,“formula”:“IIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Country Review DE].[In Progress]),NULL)”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Out of Scope”,“formula”:“IIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Country Review DE].[Out of Scope]),NULL)”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Status missing”,“formula”:“IIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Country Review DE].[(none)]),NULL)”,“format_string”:“”},{“dimension”:“Measures”,“name”:“US_COM - To-Do”,“formula”:“[Measures].[Issues with US_COM - Completion Due Date (F2C)]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“DE - Scope”,“formula”:“IIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Country Review DE].[Accepted - Use As Is]),NULL)+\nIIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Country Review DE].[Accepted - Use with Change]),NULL)+\nIIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Country Review DE].[Planned]),NULL)+\nIIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Country Review DE].[In Progress]),NULL)+\nIIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Country Review DE].[(none)]),NULL)”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Delay - COM”,“formula”:“CASE WHEN\n [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember\nTHEN\n SUM(\n PreviousPeriods(\n [Time].CurrentHierarchy.Levels("Day").CurrentDateMember),\n [Measures].[Issues with US_Com - Completion Due Date (F2C)]\n \n )\nWHEN\n DateAfterPeriodEnd("Today", [Time].CurrentHierarchyMember)\n OR\n DateInPeriod("Today", [Time].CurrentHierarchyMember)\nTHEN \n SUM(\n PreviousPeriods(\n [Time].CurrentHierarchyMember),\n [Measures].[Issues with US_COM - Completion Due Date (F2C)]\n )\nEND”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Delay - CGT”,“formula”:“CASE WHEN\n [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember\nTHEN\n SUM(\n PreviousPeriods(\n [Time].CurrentHierarchy.Levels("Day").CurrentDateMember),\n [Measures].[Issues with US_CGT - Completion Due Date (F2C)]\n \n )\nWHEN\n DateAfterPeriodEnd("Today", [Time].CurrentHierarchyMember)\n OR\n DateInPeriod("Today", [Time].CurrentHierarchyMember)\nTHEN \n SUM(\n PreviousPeriods(\n [Time].CurrentHierarchyMember),\n [Measures].[Issues with US_CGT - Completion Due Date (F2C)]\n )\nEND”,“format_string”:“”},{“dimension”:“Measures”,“name”:“DELAY - GTX”,“formula”:“CASE WHEN\n [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember\nTHEN\n SUM(\n PreviousPeriods(\n [Time].CurrentHierarchy.Levels("Day").CurrentDateMember),\n [Measures].[Issues with US_GTX - Completion Due Date (F2C)]\n \n )\nWHEN\n DateAfterPeriodEnd("Today", [Time].CurrentHierarchyMember)\n OR\n DateInPeriod("Today", [Time].CurrentHierarchyMember)\nTHEN \n SUM(\n PreviousPeriods(\n [Time].CurrentHierarchyMember),\n [Measures].[Issues with US_GTX - Completion Due Date (F2C)]\n )\nEND”,“format_string”:“”},{“dimension”:“Measures”,“name”:“US_COM/Amzad”,“formula”:“CASE WHEN\n [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember\nTHEN\n SUM(\n PreviousPeriods(\n [Time].CurrentHierarchy.Levels("Day").CurrentDateMember),\n ([Measures].[Issues with US_COM - Completion Due Date (F2C)],[Country Review US Com].[US_Com])\n )\nWHEN\n DateAfterPeriodEnd("Today", [Time].CurrentHierarchyMember)\n OR\n DateInPeriod("Today", [Time].CurrentHierarchyMember)\nTHEN\n SUM(\n PreviousPeriods(\n [Time].CurrentHierarchyMember),\n ([Measures].[Issues with US_COM - Completion Due Date (F2C)],[Country Review US Com].[US_Com])\n )\nEND\n”,“format_string”:“”}]}
} ],
“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”:“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”:“All but cancelled / rejected”,“dimension”:“Status”,“formula”:“Aggregate(\n Except(\n [Status].[Status].Members,\n { \n [Status].[Cancelled],\n [Status].[Rejected]\n }\n )\n)”,“format_string”:“”},{“name”:“Finance \u0026 Procurement”,“dimension”:“Functional Domain”,“formula”:“Aggregate({\n[Functional Domain].[Finance],\n[Functional Domain].[Procurement]\n})”,“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”:“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”:“Commercial”,“dimension”:“Workstream”,“formula”:“Aggregate({\n[Workstream].[ALX-R\u0026A Commercial],\n[Workstream].[COM-Customer 360],\n[Workstream].[COM-Evidence-to-Value],\n[Workstream].[COM-Lead-to-Quote],\n[Workstream].[COM-Order-to-Release],\n[Workstream].[COM-Quote-to-Contract],\n[Workstream].[COM-Release-to-Invoice],\n[Workstream].[COM-Strategy-to-Demand],\n[Workstream].[DAT-Commercial]\n})”,“format_string”:“”},{“name”:“Finance”,“dimension”:“Workstream”,“formula”:“Aggregate({\n[Workstream].[ALX-R\u0026A Finance \u0026 Procurement],\n[Workstream].[DAT-Finance \u0026 Controlling],\n[Workstream].[DAT-Customer],\n[Workstream].[FIN-Asset Accounting and Projects],\n[Workstream].[FIN-FC\u0026C (incl. Analytics)],\n[Workstream].[FIN-General Ledger and Period End],\n[Workstream].[FIN-I2C - Invoice 2 Cash],\n[Workstream].[FIN-Operations Finance],\n[Workstream].[FIN-Intercompany],\n[Workstream].[FIN-Revenue Management],\n[Workstream].[FIN-RDF],\n[Workstream].[FIN-Tax],\n[Workstream].[FIN-Treasury and Cash-Management],\n[Workstream].[FINP-Pillar: Finance]\n})”,“format_string”:“”},{“name”:“Procurement”,“dimension”:“Workstream”,“formula”:“Aggregate({\n[Workstream].[DAT-Supplier \u0026 Procurement],\n[Workstream].[PUR-Accounts Payable and Supplier Experience (SLP)],\n[Workstream].[PUR-CE and Transactional Direct Procurement],\n[Workstream].[PUR-CE and Transactional Indirect Procurement and Front door],\n[Workstream].[PUR-Strategic Procurement and S2C],\n[Workstream].[SEP-Supplier Exp - Supplier Information Mgmt],\n[Workstream].[SEP-Supplier Exp- Supplier Data Quality]\n})”,“format_string”:“”},{“name”:“Operations”,“dimension”:“Workstream”,“formula”:“Aggregate({\n[Workstream].[ALX-R\u0026A Operations],\n[Workstream].[DAT-Material \u0026 Operations],\n[Workstream].[DAT-Product \u0026 Brand],\n[Workstream].[OPS-Manufacturing - Asset Management],\n[Workstream].[OPS-Manufacturing - Manufacturing],\n[Workstream].[OPS-SCM - Demand Planning],\n[Workstream].[OPS-SCM - External Manufacturing],\n[Workstream].[OPS-SCM - Intercompany Order Management / Customs and Trade Compliance],\n[Workstream].[OPS-SCM - Lifecycle Management and Implementation],\n[Workstream].[OPS-SCM - Logistics/Warehousing],\n[Workstream].[OPS-SCM - Production planning],\n[Workstream].[OPS-SCM - Supply Planning],\n[Workstream].[OPS-TRD - Clinical Supply],\n[Workstream].[OPS-TRD - Embedded],\n[Workstream].[OPS-Quality (incl. OBBR)],\n[Workstream].[OPS-DAT - Triggered Project],\n[Workstream].[OPS-SCM - Ariba SCC],\n[Workstream].[MOM - Masters of Material]\n})”,“format_string”:“”},{“name”:“Two weeks ago”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time.Weekly].[Week].DateMembersBetween(\n ‘2 weeks ago’, ‘today’)\n)”,“format_string”:“”,“dimension_hierarchy”:“Weekly”},{“name”:“LDC - Workstreams”,“dimension”:“Workstream”,“formula”:“Aggregate({\n[Workstream].[Commercial],\n[Workstream].[Finance],\n[Workstream].[Procurement],\n[Workstream].[Operations],\n[Workstream].[Solutions],\n[Workstream].[DAT-Migration],\n[Workstream].[N/A],\n[Workstream].[Unmatch],\n[Workstream].[(none)]\n})”,“format_string”:“”},{“name”:“Solutions”,“dimension”:“Workstream”,“formula”:“Aggregate({\n[Workstream].[ALX-R\u0026A Integration],\n[Workstream].[DAT-Integration],\n[Workstream].[SOL-BPM],\n[Workstream].[SOL-Build Management],\n[Workstream].[SOL-Data],\n[Workstream].[SOL-Design Management],\n[Workstream].[SOL-Learning],\n[Workstream].[SOL-Platform Management],\n[Workstream].[SOL-Platform Management Basis],\n[Workstream].[SOL-Platform Management Integration],\n[Workstream].[SOL-R\u0026A],\n[Workstream].[SOL-Security \u0026 Authorization],\n[Workstream].[SOL-Solution Core \u0026 Integration],\n[Workstream].[SOL-Solution Methodology],\n[Workstream].[SOL-Technology \u0026 Architecture]\n})”,“format_string”:“”},{“name”:“US_Com”,“dimension”:“Country Review US Com”,“formula”:“Aggregate({\n [Country Review US Com].[In Progress],\n [Country Review US Com].[Planned],\n [Country Review US Com].[(none)]\n})”,“format_string”:“”}]
}