EazyBI - Measure relative to Page Time Filter but selecting previous year

[mateus_oliveira]

I want to compare issues created in periods of time relative to the Time selected in the Page Filter.

I need 3 measures:

- 1 with the count of issues from the period selected
(let's say, Last 30 days, which will cover '20 Jan 2025' to '20 Fev 2025')

- 1 with the count of issues from the same period but from the previous year
(so '20 Jan 2024' to '20 Fev 2024')

- 1 to calc the difference between them.

This is what I got from GPT:

Sum( Filter(
[Time].[Day].Members,
DateCompare(
[Time].CurrentMember.StartDate,
[Time].CurrentHierarchyMember.StartDate) >= 0
AND
DateCompare(
[Time].CurrentMember.StartDate,
Now()) <= 0),
[Measures].[Issues created])

It works flawlessly. However, for the 2nd measure, when I try

Sum( Filter(
[Time].[Year].Members,
DateCompare(
[Time].CurrentMember.StartDate,
DateAdd('yyyy', -1,
[Time].CurrentHierarchyMember.StartDate)) >= 0
AND
DateCompare(
[Time].CurrentMember.StartDate,
DateAdd('yyyy', -1, Now())) <= 0),
[Measures].[Issues created])

or any similar variation, it doesn't.

Funny enough, this formula brings nothing when it's supposed to bring (there are issues in the previous period relative to the selected filter), but it brings the same numbers as the 1st measure when there's nothing to bring (the project was created in Dec 2023, if I select Jan 2025 in the page filter, this measure will bring me nothing, but if I select Jan 2024 - which would mean the previous period would be Jan 2023, when the project didn't even exist), the 2nd measure will show the count of issues relative to the period selected instead of the previous year.

I tried taking out and including the time operations in multiple parts of this formula, switching from CurrentMember to CurrentHierarchyMembers, reformatting values... I've tried this for hours, but I couldn't get the formula to work, nor I know what exactly is wrong.

Any tips are appreciated... Thanks!

[Marita Norvele]

Hi @mateus_oliveira and @Pasam Venkateshwarrao

You can use eazyBI's standard "Time ago" calculations to compare measures between the current and previous year.

Here's a simple example from my instance to give you an idea:

Best,

Marita from eazyBI support

[mateus_oliveira]

Hi Marita!

Thanks! That was actually exactly what I needed... but then I got to another problem.

Basically, I need to allocate issues first based on their Deployment Date, and only if they don't have a Deployment Date, I'll allocate according to their "Created at" date.

I almost "got" this by basing a new measure on the "Issues Created Years Ago" formula, but the problem is: when I use aggregate, it seems that it also includes issues with a "Created at" date compatible with the page filter, even if their Deployment Date is not. I say this because when I drill down, I get "Total value: 11, rows count: 8".

So, after some testing, I concluded that, for what I need, which is a distinct issue count prioritizing their Deployment Date, I would have to use Sum(Filter()CASE). Could you confirm is this is the best approach?

It worked perfectly for Issues of current period. But when I try to adapt it to the previous year, for some reason, it brings the exact same number as the native "Issues created"... 

This is what I got so far:

Sum(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
NOT IsEmpty([Measures].[Issues created])),
CASE
WHEN
NOT IsEmpty([Measures].[Issue Deployment Date])
THEN ([Time].CurrentHierarchyMember.Level.DateMember(
DateAdd('yyyy', -1, [Time].[Year].CurrentHierarchyMember.MiddleDate)),
[Measures].[Issues with Deployment Date]
)
ELSE(
[Time].CurrentHierarchyMember.Level.DateMember(
DateAdd('yyyy', -1, [Time].[Year].CurrentHierarchyMember.MiddleDate)),
[Measures].[Issues created]
)
END
)

I believe I may be combining different methods of date operations, that probably don't work out of "Aggregate"... Would you help me to adapt this?

Once again, thanks for the support!

[Marita Norvele]

Hi @mateus_oliveira 

You can use the measure "Issues with deployment date" to create your MDX formula.

There are two different approaches depending on how you want to handle issues with a Created Date and Deployment Date in different years, as in the example below.

Scenario 1: count the issue for 2025 even if the deployment date is in 2026
(counted by created date in 2025)

This formula will count an issue by the creation date even if the deployment date falls in a different year. For example, if an issue was created in 2025 but has deployment date in 2026, it will be counted in 2025.

Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    not IsEmpty([Measures].[Issue deployment date])
  ),
  CASE
  WHEN
  [Measures].[Issues with deployment date] > 0 THEN
  [Measures].[Issues with deployment date]
  ELSE
  [Measures].[Issues created]
  END
)

Make sure to change Formatting to "Numeric" for this formula.

Scenario 2: count the issue for 2025 only if the deployment date is in the same year.

This formula will only count an issue in the year of its deployment date. Using the same example - issue created in 2025 with deployment date in 2026 will only be counted in 2026.

Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    not IsEmpty([Measures].[Issue deployment date])
  ),
  CASE
  WHEN
  [Measures].[Issues with deployment date] > 0
  THEN
  1
  WHEN  
  [Measures].[Issues with deployment date] > 0 AND
  [Measures].[Issues created] >0
  THEN
  1  
  END
)

Make sure to change Formatting to "Numeric" for this formula.

You can choose either formula depending on what results you are looking & you can apply "Time ago" function for the measure.

I hope this helps.

Best,
Marita from eazyBI support

[mateus_oliveira]

Hi Marita!

Thanks for your patience and your support.

It did help, as I understood that the most import thing is getting the correct filter to the current data, the I'll just need to use the "Time Ago" Function, I honestly had already forgot this was the best approach.

But for some reason I'm still not getting what I need from the Current Time Period. All the measures I tried, including this one you sent, are not including some issues with Deployment Date set for a corresponding period to that in the Page Filter, when their Created At dates are outside the period, or not including some issues without any Deployment Date but with a Created At date that do corresponds... Some are correctly included, some are missing. Is there any filter related to the status of the issue, or if the issue was resolved? I say this because I wasn't able to identify any pattern to differentiate why some issues were included but some others that also fitted the criteria were not, except for the status - it seems that any issue that is not "Opened" or "Done"/"Canceled" are not being included.

Here, I wanna make sure I'm clear about what I'm trying to achieve, Just in case I may be asking for help without giving clear details.

I need to aggregate all the Issues with a "Deployment Date" corresponding to the time period set in the Page Filter. IF the issue doesn't have a Deployment Date, THEN I'll consider it according to its Created At date. A lot of issues don't have any Deployment Date set. I don't care about the issue status (opened, in progress, paused, review, canceled, done, resolved, unresolved, etc), all I want is to gather all the issues by the deployment date, if corresponding, or by they created date, if corresponding and they don't have a deployment date set.

I got to two separate formulas that successfully get all the issues without including issues that fit the other criteria: 1 for ALL the issues with a Deployment Date corresponding to the time period in the Page Filter, 1 for ALL the issues WITHOUT Deployment Date, with a Created At date that corresponds to the Page Filter - but I have no idea if it's possible to combine them, and how to do it. But I know for a fact that they are including, in their respective filters, issues that fits my criteria 

This gathers issues with a Deployment Date corresponding to the Page Filter:

Sum(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
not IsEmpty([Measures].[Issue deployment date])),
CASE
WHEN
NOT IsEmpty([Measures].[Issue Deployment Date])
THEN ([Time].[Year].CurrentHierarchyMember,
[Measures].[Issues with Deployment Date])
ELSE (NULL)
END)

This one gathers issues without any Deployment Date but with a Created At date corresponding to the Page Filter:

Sum(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
NOT IsEmpty([Measures].[Issues created])),
CASE
WHEN
IsEmpty([Measures].[Issue Deployment Date])
THEN ([Time].[Year].CurrentHierarchyMember,
[Measures].[Issues Created])
ELSE (NULL)
END)

As you see, I used two different filters. I'm not even sure if I'm using the correct CASE WHEN syntax, but those two work just as I need... Is there how to aggregate both of them?

I'm really sorry for taking that much of your time, but only here I'm getting closer to I need...

Once again, thanks! 

[Marita Norvele]

Hi Mateus,

Thanks for the follow-up message and explanation. 

You could try the following formula: 

Count(
 Filter(
  Descendants([Issue].CurrentMember, [Issue].[Issue]),
CASE
WHEN
 IsEmpty([Measures].[Issue Deployment Date])
THEN
 [Measures].[Issues created] >0
ELSE
 [Measures].[Issue Deployment Date] >0
END
))

For each issue, it checks:

• If the issue doesn’t have a deployment date, it considers issue created date when Time dimension is used
• If the issue does have a deployment date, it considers the issue deployment date when Time dimension is used

Let me know if this works for you. 

Best, 
Marita from eazyBI support

[Pasam Venkateshwarrao]

Hi @mateus_oliveira 

Thanks for reaching out to community.

Predefined measure [Open issues] shows a number of all unresolved issues at the end of the corresponding Time dimension period (regardless of when they were created). Also, [Issues resolved] shows a number of all resolved issues in the time period (regardless of when they were created).

To see the number of issues created and resolved in the same time period, create a new report-specific calculated measure [Resolved same period] with the following formula:

CoalesceEmpty(Sum(
Filter(
-- iterate through set of issues
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
-- apply filter criteria to each issue
DateInPeriod(
[Measures].[Issue created date],
[Time].CurrentHierarchyMember ) AND
DateInPeriod(
[Measures].[Issue resolution date],
[Time].CurrentHierarchyMember )
),
-- numeric expression - sum of relevant issues
[Measures].[Issues created]
),0)

Then, you can create one more calculation to see the number of issues that were not resolved in the same period as they were created (I suggest not using the name “Open issues” because it will be confused with a predefined measure):

[Measures].[Issues created] - [Measures].[Resolved same period]

Hope this helps

[mateus_oliveira]

Thanks! But I actually don't need to distinct "Solved Issues" nor their status. I just need to count them according to their dates (1st Deployment Date, if not set, then Created at date)

But I'll definitely save this for later! :)