Help on mdx measures for planned until today and passed until today

Another help please with the mdx measure,

image

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

Hi,

The following formula is for counting all the Issues with due dates till today:

Sum(
  PreviousPeriods([Time].[Day].CurrentDateMember),
  [Measures].[Issues with due date]
)

As I can see from your report, your use case requires counting issues in specific statuses only.
For that, you can create an Aggregated member in the status dimension and use a tuple in the formula referring to this aggregated member (e.g., Cycle statuses):

Sum(
  PreviousPeriods([Time].[Day].CurrentDateMember),
  ([Measures].[Issues with due date],[Status].[Cycle statuses])
)

You can create a similar calculation to find the passed issues by creating another aggregated member in the status dimension (with the statuses Passed, Cancelled).

Kindly,
Janis, eazyBI support

1 Like