Filter Member in Tuple by End Date

Questions & Answers
1

Hi,
I need a Measure, which prints “1” in row cell*, when Member is in this filter:

issuetype = CTE and enddate > "2023-03-15"
  1. I’ve Imported “End Date” as Measure,Property and can deal with it as
[Issue].CurrentHierarchyMember.get('End date')
[Measures].[Issues with End date] --printed in report table like "Apr 04 2022"
  1. I can see that this Tuple mostly works as I need
(
[Measures].[Issues with End date],
[Issue Type].[CTE],
[Time].[2023].[Q1 2023].[Mar 2023]
)

but how to filter this Tuple with enddate > "2023-03-15"?

*the structure of levels I’m working with you can see in this thread
https://community.eazybi.com/t/total-of-descendants-in-collapsed-expanded-view/11353

2

Hi,

Great that you have imported this date both as a property and a measure!

If you have issues in the report rows, you can use CASE WHEN construction where you compare the imported End date property with your target date and return 1 if, for this issue, the end date is larger than March 15, 2023. For comparing dates, use the DateCompare function

case WHEN
 DateCompare([Issue].CurrentHierarchyMember.get('End date'), 
 "2023-03-15")>0
THEN
 1
end

Also, you may add the “Issue type” property in columns and filter by this column: Regular expressions

Best,
Ilze , support@eazybi.com

3

Thank you a lot,

this works.

And the Sum for Measure appears in Total.

But trully I have extra case - about Tuples. It’s described and answered in
https://community.eazybi.com/t/total-of-descendants-in-collapsed-expanded-view/11353/4

Shortly - when I use Tuple in Measure, Sum is printed at head of report(“ALL INITIATIVES” hierarhy),
so this works (measure field “Tuple in Measure”):

(
[Issue Type].[CTE],
[ifStructure].[PI_2023.2]
)

image

Then, I’ve added you code as Measure (measure field “EndDate by EB Team”)

case WHEN
DateCompare([Issue].CurrentHierarchyMember.get(‘End date’),
“2023-03-15”)>0
THEN
1
end

and tried to get it in Tuple (measure field “Tuple in Measure+EndDate”)

(
[Measures].[endDate by EB team]
[Issue Type].[CTE],
[ifStructure].[PI_2023.2]
)

and this’s not Summing in head of report.

image

Ok, I’ve tried to return in your code a Member instead of Number “1”.

case WHEN
 DateCompare([Issue].CurrentHierarchyMember.get('End date'), 
 "2023-03-15")>0
THEN
--1 --return Integer
[Issue Type].[CTE] --Let's return some Member
end

still the same result
image

But in general Total is printed, and it’s good.

image
image1043×39 1.24 KB

I believe, I’m missing some key concepts…

4

Hi @cybertachikoma

The given calculation formula works only if issues are selected in the report rows, as it uses issue property to detect if the issue has an end date after Mar 15, 2023.
When issues are not selected in the report, issue properties can not be directly retrieved; therefore, when you use this calculated measure in a tuple with something else selected in the report rows, the result is empty.

You may try a different approach. Instead of using the issue property in the calculation, you may use the measure “Issues with End date” that counts issues having an end date in a selected time period (when used together with TIme dimension): combine it in a calculation formula with a time period after Mar 15, 2023

The following calculation still should return 1 for the issue if it has the End date after Mar 15, 2023; meanwhile, it will return the total count of such issues also if issues are not selected in the report:

Sum(
  Filter(
  [Time].[Day].Members,
  DateCompare("2023-03-15",[Time].CurrentHierarchyMember.StartDate)<0),
  [Measures].[Issues with End date]
)

Now you should be able to use this measure in the tuple.

Best,
llze, support@eazybi.com

1 Like