Another help please with the mdx measure,
I am trying to build the report as in the above screenshot
Report Definition as below
{
"cube_name": "Issues",
"cube_reports": [ {
"name": "Overall Progress Under construction 2",
"folder_name": "Work In-Progress",
"result_view": "table",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Total Scope]","[Measures].[Planned Till Today]","[Measures].[Passed]","[Measures].[Passed Before Due date]","[Measures].[Passed After Due Date]","[Measures].[In Progress.]","[Measures].[On Hold]","[Measures].[Failed]"],"members":[{"depth":0,"full_name":"[Measures].[Total Scope]","format_string":"#,##0","report_specific":true,"name":"Total Scope","calculated":true},{"depth":0,"full_name":"[Measures].[Planned Till Today]","format_string":"#,##0","report_specific":true,"name":"Planned Till Today","calculated":true},{"depth":0,"full_name":"[Measures].[Passed Before Due date]","format_string":"#,##0","report_specific":true,"name":"Passed Before Due date","calculated":true,"id":"[Measures].[Passed Before Due date]"},{"depth":0,"full_name":"[Measures].[Passed After Due Date]","format_string":"#,##0","report_specific":true,"name":"Passed After Due Date","calculated":true,"id":"[Measures].[Passed After Due Date]"}]}]},"rows":{"dimensions":[{"name":"Functional Domain","selected_set":["[Functional Domain].[LAT Functions]"],"members":[{"depth":0,"name":"LAT Functions","full_name":"[Functional Domain].[LAT Functions]","calculated":true,"drillable":true,"dimension":"Functional Domain","expanded":true,"drilled_into":false}],"bookmarked_members":[{"depth":1,"name":"Finance","full_name":"[Functional Domain].[Finance]"},{"depth":1,"name":"Procurement","full_name":"[Functional Domain].[Procurement]"}]}]},"pages":{"dimensions":[{"name":"Status","selected_set":["[Status].[All Statuses]"],"members":[{"depth":0,"name":"All Statuses","full_name":"[Status].[All Statuses]","drillable":true,"type":"all"}],"bookmarked_members":[],"current_page_members":["[Status].[All Statuses]"]},{"name":"Time","selected_set":["[Time].[All Times]"],"members":[{"depth":0,"name":"All Times","full_name":"[Time].[All Times]","drillable":true,"type":"all"}],"bookmarked_members":[],"current_page_members":["[Time].[All Times]"]},{"name":"Project","selected_set":["[Project.Category].[Novartis PMO].[LDC Project Management]"],"members":[{"depth":2,"name":"LDC Project Management","full_name":"[Project.Category].[Novartis PMO].[LDC Project Management]","drillable":true,"key":"LDCPM"}],"bookmarked_members":[{"depth":2,"name":"LDC Project Management","full_name":"[Project.Category].[Novartis PMO].[LDC Project Management]","drillable":true,"key":"LDCPM"}],"current_page_members":["[Project.Category].[Novartis PMO].[LDC Project Management]"]},{"name":"Issue Type","selected_set":["[Issue Type].[Test Instance]"],"members":[{"depth":1,"name":"Test Instance","full_name":"[Issue Type].[Test Instance]"}],"bookmarked_members":[{"depth":1,"name":"Test Instance","full_name":"[Issue Type].[Test Instance]"}],"current_page_members":["[Issue Type].[Test Instance]"]},{"name":"Functional Domain","duplicate":true,"selected_set":["[Functional Domain].[LAT Functions]"],"members":[{"depth":0,"name":"LAT Functions","full_name":"[Functional Domain].[LAT Functions]","calculated":true,"drillable":true,"dimension":"Functional Domain"}],"bookmarked_members":[],"current_page_members":["[Functional Domain].[LAT Functions]"]},{"name":"Workstream","selected_set":["[Workstream].[All Workstreams]"],"members":[{"depth":0,"name":"All Workstreams","full_name":"[Workstream].[All Workstreams]","drillable":true,"type":"all"}],"bookmarked_members":[],"current_page_members":["[Workstream].[All Workstreams]"]},{"name":"Country Relevance","selected_set":["[Country Relevance].[All Country Relevances]"],"members":[{"depth":0,"name":"All Country Relevances","full_name":"[Country Relevance].[All Country Relevances]","drillable":true,"type":"all"}],"bookmarked_members":[],"current_page_members":["[Country Relevance].[All Country Relevances]"]}]},"options":{},"view":{"current":"table","maximized":false,"table":{"cell_formatting":{}}},"calculated_members":[{"dimension":"Measures","name":"Total Scope","formula":"Sum(\n { PreviousPeriods([Time].CurrentHierarchyMember),\n [Time].CurrentHierarchyMember },\n [Measures].[Issues with due date]\n)","format_string":"#,##0"},{"dimension":"Measures","name":"Planned Until Date","formula":"NonZero(\nSUM(\nFilter(\n[Issue].[Issue].Members,\nDateCompare(\n[Issue].CurrentMember.get('Due date'),\nNow()\n) \u003c 0\n),\n[Measures].[Issues due]\n)\n)","format_string":""},{"dimension":"Measures","name":"LAT % ","formula":"([Measures].[Passed]/[Measures].[Planned Till Today])","format_string":"#0%"},{"dimension":"Measures","name":"Planned Till Today","formula":"NonZero(count(\nFilter(Descendants([Issue].CurrentMember,[Issue].[Issue]),\n[Measures].[Issues with due date] \u003e0 and\n([Status].[NO RUN]+\n[Status].[In Progress]+\n[Status].[On Hold]+\n[Status].[Failed])AND\nDateCompare([Measures].[Issue due date],\"Today\")\u003c0\n)\n))","format_string":""},{"dimension":"Measures","name":"Planned till today and completed","formula":"DateCompare([Issue].CurrentMember.get('Due date'), \"Today\")\n","format_string":"#,##0"},{"dimension":"Measures","name":"LAT %2","formula":"([Measures].[Passed]/[Measures].[Planned Until 7 days test])","format_string":"#0%"},{"dimension":"Measures","name":"Passed After Due Date","formula":"NonZero(\n Count(\n (Filter(\n Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),\n DateInPeriod(\n [Measures].[Issue due date],\n [Time].CurrentHierarchyMember\n )\n AND\n [Measures].[Issues with due date]\u003e0\n AND\n DateCompare(\n ([Measures].[Transition to status last date],\n [Transition Status].[Passed],\n [Time].DefaultMember),\n [Measures].[Issue due date]\n )\u003e0\n )\n )\n )\n)","format_string":"#,##0"},{"dimension":"Measures","name":"Passed Before Due date","formula":"NonZero(\n Count(\n (Filter(\n Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),\n DateInPeriod(\n [Measures].[Issue due date],\n [Time].CurrentHierarchyMember\n )\n AND\n [Measures].[Issues with due date]\u003e0\n AND\n DateCompare(\n ([Measures].[Transition to status last date],\n [Transition Status].[Passed],\n [Time].DefaultMember),\n [Measures].[Issue due date]\n \n )\u003c=0\n )\n )\n )\n)","format_string":"#,##0"},{"dimension":"Measures","name":"Overdue Test ","formula":"CASE WHEN\n [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember\nTHEN\n-- when no time dimension is used in the report get today\n SUM(\n PreviousPeriods(\n [Time].CurrentHierarchy.Levels(\"Day\").CurrentDateMember),\n [Measures].[Issues due]\n --example reusable for any date custom field use instead of measure Issued due\n -- ([Measures].[Issues with due date],\n -- [Resolution].[(unresolved)])\n )\nWHEN\n-- with time dimension in the report apply the calcualtion for past dates and today only\n DateAfterPeriodEnd(\"Today\", [Time].CurrentHierarchyMember)\n OR\n DateInPeriod(\"Today\", [Time].CurrentHierarchyMember)\nTHEN \n SUM(\n PreviousPeriods(\n [Time].CurrentHierarchyMember),\n [Measures].[Issues due]\n --example reusable for any date custom field use instead of measure Issued due\n -- ([Measures].[Issues with due date],\n -- [Resolution].[(unresolved)])\n )\nEND","format_string":""},{"dimension":"Measures","name":"Passed Until Today","formula":"NonZero(count(\nFilter(Descendants([Issue].CurrentMember,[Issue].[Issue]),\n[Measures].[Issues with due date] \u003e0 and\n([Status].[Passed]+\n[Status].[Cancelled])AND\nDateCompare([Measures].[Issue due date],\"Today\")\u003c0\n)\n))","format_string":""}]}
} ],
"calculated_members": [{"dimension":"Measures","name":"Transition to status last date","format_string":"yyyy-mm-dd hh:nn:ss","formula":"TimestampToDate(\n [Measures].[Transition to status last timestamp]\n)"},{"dimension":"Measures","name":"Issue due date","format_string":"mmm dd yyyy","formula":"[Issue].CurrentHierarchyMember.get('Due date')"},{"name":"LAT Functions","dimension":"Functional Domain","formula":"Aggregate({\n[Functional Domain].[Commercial],\n[Functional Domain].[Operations],\n[Functional Domain].[Finance],\n[Functional Domain].[Procurement],\n[Functional Domain].[Manufacturing],\n[Functional Domain].[QA],\n[Functional Domain].[SCM],\n[Functional Domain].[Unmatch]\n})","format_string":""},{"name":"Failed","dimension":"Measures","formula":"[Status].[Failed]","format_string":""},{"name":"In Progress.","dimension":"Measures","formula":"[Status].[In Progress]","format_string":""},{"name":"On Hold","dimension":"Measures","formula":"[Status].[On Hold]","format_string":""},{"name":"Passed","dimension":"Measures","formula":"[Status].[Passed]","format_string":"#,##0"}]
}
I want the mdx measure where I want to show the Planned Until Today as issues with duedate with current date and Passed until today is issues which are in passed status till today.
Can you please help me with the mdx measures for the same both Planned Untill Today and Passed until today to get the correct numbers. I have tried from my side the numbers are not correct.
Thanks