Original Message:
Sent: Fri February 28, 2025 02:24 AM
From: Marc Reed
Subject: Last year Sales based on current year
@Philipp Hornung you probably know this, others may not...
You can trick relative date functionality to work for any column by using the validate option. No need to convert anything to a date.
For example, if in Go Sales I want to create measures for different product lines.
Firstly I create filters on the product dimension for each product line such as this:

I can then edit the Product Line data item and add in a validate to make the DM think it's a date.

You can see this makes the DM think it's a date:

You can then create relative measures as usual. Such as:

Once it's all up and running, remove the validate.
------------------------------
Marc Reed
Reporting Lead
Original Message:
Sent: Thu February 27, 2025 11:37 AM
From: Philipp Hornung
Subject: Last year Sales based on current year
@IAN HENDERSON The more I get used to relative date filters (copy & paste & adjust for each use case) the more I consider unions and multiple queries as archaic and not sustainable. Relative date filters are defined ONCE and can be applied to ALL measures with 4 clicks only. And they are wokring for all related reports really the same way.
What I don't understand is why you restricted this functionality to date columns only. There is also potential for other use cases like measure by status etc. I often convert non-date-values to dummy-dates and back in the mapping-table to use measure filters.
------------------------------
Philipp Hornung
Product Owner & BI Manager
Techniker Krankenkasse
Hamburg Germany
#IBMChampion
Original Message:
Sent: Thu February 27, 2025 09:57 AM
From: IAN HENDERSON
Subject: Last year Sales based on current year
No, I have not written anything about the topic.
Here is some documentation about it, including some about how to customize relative time filters.
https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=modules-relative-date-analysis
https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=analysis-creating-relative-date-filters
Knowing the data source you are using could be helpful. In some cases there is functionality to define relative time in some manner, which would allow you to not try to reinvent the wheel.
As Philipp has stated, data modules have functionality to define relative time filters. Dynamic cubes and PowerCubes too. FM models do not but there are ways to deal with filtering a report to a set of time periods. Milan has provided an example. Two actually. Also Trevor has suggested something but you have not indicated how it is not appropriate to your situation.
Knowing what metadata is available to you could be helpful to how you approach the problem but I think the pattern which Milan has given you could be appropriate to your circumstances.
I think it is of some importance to point out that 'it is not working' or similar statements do not provide sufficient information to anyone to diagnose the situation and provide further advice.
------------------------------
IAN HENDERSON
Original Message:
Sent: Thu February 27, 2025 02:44 AM
From: Philipp Hornung
Subject: Last year Sales based on current year
Hi Max,
Again for relational data sources the most sustainable solution I can imagine would be a data module using relative date measure filters with a parameter. I tried to find a smooth post regarding relative date filters but didn't succeed. @IAN HENDERSON did you write a blog post or documentation about this?
Best,
Philipp
------------------------------
Philipp Hornung
Product Owner & BI Manager
Techniker Krankenkasse
Hamburg Germany
#IBMChampion
Original Message:
Sent: Wed February 26, 2025 07:50 AM
From: Max Ray
Subject: Last year Sales based on current year
Hi,
I have a report where the sales data is displayed based on the period selected via the prompt page. I would like to add a new column, "Sales (Last Year)," which will display the sales for the same month in the previous year, relative to the period entered in the prompt.
For example, if the user enters the period as 202502, the "Sales (Last Year)" column will show sales for the period 202412. Similarly, if the user enters 202404 as the current period, the "Sales (Last Year)" column will display sales for the period 202312.
my current expression is for calculating the sales is
tOTAL(IF( [Period Num]= ?Period? and [Country] = 'USA') THEN (Sales) ELSE (0))
Could you please modify this expression for last year last month period?
Thanks in advance.
------------------------------
Max Ray
------------------------------