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!

[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! :)