Need help in mdx measure

Hi,
I am trying to replicate the below mdx measure for overdue issues,

Report is responding slow while using this measure.

NonZero(Count(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
[Measures].[Issues with due date]>0 and
IsEmpty([Measures].[Issues resolved]) AND
DateCompare([Measures].[Issue due date],Now())<0
)
))

I am trying to get the same numbers in the report using the below mdx measure.
Reason, I was change the measure is by using this below measure in the report, report is loading faster than while using the above measure.

CASE WHEN
DateAfterPeriodEnd(“Today”, [Time].CurrentHierarchyMember)
OR
DateInPeriod(“Today”, [Time].CurrentHierarchyMember)
THEN
NonZero(SUM(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
– filters all issues with due date in period
DateInPeriod(
[Measures].[Issues with due date]>0,
[Time].CurrentHierarchyMember)
AND
– filter to see if due date is before resolution date or till today
IIF(
IsEmpty([Measures].[Issues resolved]),
DateCompare([Measures].[Issues with due date],Now())<0,
DateCompare(
[Measures].[Issues with due date],
DateWithouttime([Measures].[Issues resolved])
)
),
( [Measures].[Issues created count],
[Time].CurrentHierarchy.DefaultMember )
))
END

Please help me with the measure to get the same numbers what I am getting by using the measure #1

Hi,

The main reason why such calculations are slow is the need to iterate the Issue dimension. Your alternative calculation responds faster because it avoids the calculation for future periods but generally has the same performance complexity.

Such measures are slightly more efficient if the formula can avoid checking the measures but first checks the issue properties.

So, the following formula should work identically and be more efficient:

CASE WHEN
DateAfterPeriodEnd("Today", [Time].CurrentHierarchyMember)
OR
DateInPeriod("Today", [Time].CurrentHierarchyMember)
THEN
NonZero(Count(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
    DateInPeriod([Measures].[Issue due date],[Time].CurrentHierarchyMember)
  AND
    IsEmpty([Measures].[Issues resolved]) 
  AND
    DateCompare([Measures].[Issue due date],Now())<0
)
))
END

The main reason for the performance, however, is the need for iteration of all the issues.

Kindly,

Thanks for the information. That worked.

I have another mdx measure, which really slows the report.

NonZero(
Sum(
Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
IsEmpty([Measures].[Issue due date])
)
)
)

While using the above measure in my report the report performance is very very slow, can you please help me with the mdx measure where I can get the same numbers in the report as using the above measure.

Below is the definition of the report

{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “Integrated Applications”,
“folder_name”: “LDC - Build - IT”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:[“[Measures].[Total ]”,“[Measures].[Overdue ]”,“[Measures].[Blank Due Date]”,“[Measures].[Closed ]”,“[Measures].[In Review ]”,“[Measures].[In Progress ]”,“[Measures].[On Hold ]”,“[Measures].[Open ]”],“members”:[{“depth”:0,“full_name”:“[Measures].[Total ]”,“format_string”:“#,##0”,“report_specific”:true,“name”:“Total “,“calculated”:true},{“depth”:0,“full_name”:”[Measures].[Overdue ]”,“format_string”:“#,##0”,“report_specific”:true,“name”:“Overdue “,“calculated”:true,“id”:”[Measures].[Overdue ]”},{“depth”:0,“full_name”:“[Measures].[Blank Due Date]”,“format_string”:“#,##0”,“report_specific”:true,“name”:“Blank Due Date”,“calculated”:true},{“depth”:0,“full_name”:“[Measures].[Closed ]”,“format_string”:“”,“report_specific”:true,“name”:“Closed “,“calculated”:true},{“depth”:0,“full_name”:”[Measures].[In Review ]”,“format_string”:“”,“report_specific”:true,“name”:“In Review “,“calculated”:true},{“depth”:0,“full_name”:”[Measures].[In Progress ]”,“format_string”:“”,“report_specific”:true,“name”:“In Progress “,“calculated”:true},{“depth”:0,“full_name”:”[Measures].[On Hold ]”,“format_string”:“”,“report_specific”:true,“name”:“On Hold “,“calculated”:true},{“depth”:0,“full_name”:”[Measures].[Open ]”,“format_string”:“”,“report_specific”:true,“name”:“Open “,“calculated”:true}]}]},“rows”:{“dimensions”:[{“name”:“Milestone”,“selected_set”:[”[Milestone].[ACD-010 Integrated Plan with 3rd party confirmed​]”,“[Milestone].[ACD-020 3rd party resources onboarded​]”,“[Milestone].[ACD-030 SoW/Addendum/CR signed \u0026 approved​]”,“[Milestone].[ACD-040 3rd Party Risk Checklist completed]”,“[Milestone].[DDD-020 3rd Party Technical Design complete​]”,“[Milestone].[DDD-030 Connections established​]”,“[Milestone].[DDD-040 Integrated Screening in DEV with 3rd party complete​]”,“[Milestone].[DDD-050 Test scope / approach aligned and confirmed]”,“[Milestone].[DDD-090 Build Tollgate passed on 3rd party side (Triggered projects only)​]”,“[Milestone].[DDD-100 Define customer communication related to the 3rd party changes and align / integrate with]”,“[Milestone].[TA-010 GAT PREP Preparation for GAT ]”,“[Milestone].[TA-020 LAT PREP Preparation for LAT ]”,“[Milestone].[TA-030 GAT Completion]”,“[Milestone].[TA-040 LAT Completion]”,“[Milestone].[DG-010 CUT PREP DG-010 CUT PREP Cutover Preparation]”,“[Milestone].[DG-020 GL PREP DG-020 GL PREP Go-live Preparation]”],“members”:,“bookmarked_members”:}]},“pages”:{“dimensions”:[{“name”:“Project”,“selected_set”:[“[Project].[All Projects]”],“members”:[{“depth”:0,“name”:“All Projects”,“full_name”:“[Project].[All Projects]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false,“removed”:true},{“depth”:1,“name”:“Lean Digital Core”,“full_name”:“[Project].[Lean Digital Core]”,“drillable”:true,“key”:“CDS”,“expanded”:true,“drilled_into”:false,“parent_full_name”:“[Project].[All Projects]”},{“depth”:2,“name”:“(no component)”,“full_name”:“[Project].[Lean Digital Core].[(no component)]”,“url_id”:-1,“parent_key”:“CDS”,“removed”:true,“parent_full_name”:“[Project].[Lean Digital Core]”},{“depth”:2,“name”:“Dev - ABAP”,“full_name”:“[Project].[Lean Digital Core].[Dev - ABAP]”,“url_id”:15386,“parent_key”:“CDS”,“removed”:true,“parent_full_name”:“[Project].[Lean Digital Core]”},{“depth”:2,“name”:“Dev - CPI”,“full_name”:“[Project].[Lean Digital Core].[Dev - CPI]”,“url_id”:15385,“parent_key”:“CDS”,“removed”:true,“parent_full_name”:“[Project].[Lean Digital Core]”},{“depth”:2,“name”:“Dev - FIORI”,“full_name”:“[Project].[Lean Digital Core].[Dev - FIORI]”,“url_id”:15363,“parent_key”:“CDS”,“removed”:true,“parent_full_name”:“[Project].[Lean Digital Core]”},{“depth”:2,“name”:“�”,“full_name”:“[Project].[Lean Digital Core].[�]”,“url_id”:15528,“parent_key”:“CDS”,“removed”:true,“parent_full_name”:“[Project].[Lean Digital Core]”},{“depth”:2,“name”:“A-DIT_Data Hub (43653)”,“full_name”:“[Project].[Lean Digital Core].[A-DIT_Data Hub (43653)]”,“url_id”:15799,“parent_key”:“CDS”,“removed”:true,“parent_full_name”:“[Project].[Lean Digital Core]”}],“bookmarked_members”:,“current_page_members”:[“[Project].[Lean Digital Core]”]},{“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}],“bookmarked_members”:,“current_page_members”:[“[Functional Domain].[LDC - Verticals]”]},{“name”:“Status”,“selected_set”:[“[Status].[Not Cancelled]”,“[Status].[Not Cancelled/Completed]”],“members”:[{“depth”:0,“name”:“Not Cancelled/Completed”,“full_name”:“[Status].[Not Cancelled/Completed]”,“calculated”:true,“drillable”:true,“dimension”:“Status”}],“bookmarked_members”:,“current_page_members”:[“[Status].[Not Cancelled/Completed]”]}]},“options”:{“total”:“rows”},“view”:{“current”:“table”,“maximized”:false,“table”:{}},“calculated_members”:[{“dimension”:“Measures”,“name”:“Total “,“formula”:”([Measures].[Issues created],[Status].[Not Cancelled])”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Overdue “,“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 due date]\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 due date]\n )\nEND”,“format_string”:””},{“dimension”:“Measures”,“name”:“Closed “,“formula”:”[Status].[Closed]+\n[Status].[Done]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“In Review “,“formula”:”[Status].[In Review]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“In Progress “,“formula”:“Aggregate({\n[Status].[In Progress],\n[Status].[Re-opened]\n})”,“format_string”:””},{“dimension”:“Measures”,“name”:“On Hold “,“formula”:”[Status].[On Hold]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Open “,“formula”:”[Status].[Open]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Blank Due Date”,“formula”:“NonZero(\n Sum(\n Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),\n IsEmpty([Measures].[Issue due date])\n )\n )\n)”,“format_string”:“#,##0”}]}
} ],
“calculated_members”: [{“dimension”:“Measures”,“name”:“Issue due date”,“format_string”:“mmm dd yyyy”,“formula”:“[Issue].CurrentHierarchyMember.get(‘Due date’)”},{“name”:“Not Cancelled”,“dimension”:“Status”,“formula”:“Aggregate(\n Except(\n [Status].[Status].Members,\n { \n [Status].[Cancelled]\n }\n )\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].[PLM],\n[Functional Domain].[Solution],\n[Functional Domain].[Data],\n[Functional Domain].[Manufacturing],\n[Functional Domain].[QA],\n[Functional Domain].[SCM],\n[Functional Domain].[TRD],\n[Functional Domain].[Reporting \u0026 Analytics],\n[Functional Domain].[Unmatch]\n\n})”,“format_string”:“”},{“name”:“ACD-010 Integrated Plan with 3rd party confirmed​”,“dimension”:“Milestone”,“formula”:“[Milestone].[ACD-010 Plan​]”,“format_string”:“”},{“name”:“ACD-020 3rd party resources onboarded​”,“dimension”:“Milestone”,“formula”:“[Milestone].[ACD-020 3rd RSRC​]”,“format_string”:“”},{“name”:“ACD-030 SoW/Addendum/CR signed \u0026 approved​”,“dimension”:“Milestone”,“formula”:“[Milestone].[ACD-030 SoW​]”,“format_string”:“”},{“name”:“ACD-040 3rd Party Risk Checklist completed”,“dimension”:“Milestone”,“formula”:“[Milestone].[ACD-040 3rd RISK​]”,“format_string”:“”},{“name”:“DDD-020 3rd Party Technical Design complete​”,“dimension”:“Milestone”,“formula”:“[Milestone].[DDD-020 3rd FS​]”,“format_string”:“”},{“name”:“DDD-030 Connections established​”,“dimension”:“Milestone”,“formula”:“[Milestone].[DDD-030 CONN​]”,“format_string”:“”},{“name”:“DDD-040 Integrated Screening in DEV with 3rd party complete​”,“dimension”:“Milestone”,“formula”:“[Milestone].[DDD-040 INT SCRN​]”,“format_string”:“”},{“name”:“DDD-050 Test scope / approach aligned and confirmed”,“dimension”:“Milestone”,“formula”:“[Milestone].[DDD-050 TST APPR​]”,“format_string”:“”},{“name”:“DDD-090 Build Tollgate passed on 3rd party side (Triggered projects only)​”,“dimension”:“Milestone”,“formula”:“[Milestone].[DDD-090 BLD TGR TRPRJ​]”,“format_string”:“”},{“name”:“DDD-100 Define customer communication related to the 3rd party changes and align / integrate with”,“dimension”:“Milestone”,“formula”:“[Milestone].[DDD-100 Comms​]”,“format_string”:“”},{“name”:“Not Cancelled/Completed”,“dimension”:“Status”,“formula”:“Aggregate(\n Except(\n [Status].[Status].Members,\n { \n [Status].[Cancelled],\n [Status].[Completed],\n [Status].[Done]\n }\n )\n)”,“format_string”:“”},{“name”:“TA-010 GAT PREP Preparation for GAT “,“dimension”:“Milestone”,“formula”:”[Milestone].[TA-010 GAT PREP]”,“format_string”:“”},{“name”:“TA-020 LAT PREP Preparation for LAT “,“dimension”:“Milestone”,“formula”:”[Milestone].[TA-020 LAT PREP]”,“format_string”:“”},{“name”:“TA-030 GAT Completion”,“dimension”:“Milestone”,“formula”:“[Milestone].[TA-030 GAT]”,“format_string”:“”},{“name”:“TA-040 LAT Completion”,“dimension”:“Milestone”,“formula”:“[Milestone].[TA-040 LAT]”,“format_string”:“”},{“name”:“DG-010 CUT PREP DG-010 CUT PREP Cutover Preparation”,“dimension”:“Milestone”,“formula”:“[Milestone].[DG-010 CUT PREP]”,“format_string”:“”},{“name”:“DG-020 GL PREP DG-020 GL PREP Go-live Preparation”,“dimension”:“Milestone”,“formula”:“[Milestone].[DG-020 GL PREP]”,“format_string”:“”}]
}

Hi,

It is possible to rewrite the formula counting issues without a due date. If the report does not use Time dimension, the following formula should work identically:

[Measures].[Issues created]-[Measures].[Issues with due date]

Kindly,
Janis, eazyBI support

1 Like

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