I want to compare issues created in periods of time relative to the Time and 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!