Selection of year has to show sales value from start of the year until current month.

[vikasshana]

Hi,

I've below data.

Date	Sales
24-12-2024	100
24-11-2024	200
24-10-2024	300
24-09-2024	400
24-08-2024	300
24-07-2024	200
24-06-2024	600
24-05-2024	700
24-04-2024	100
24-03-2024	700
24-02-2024	500
24-01-2024	800
24-12-2023	88
24-11-2023	300
24-10-2023	103
24-09-2023	492
24-08-2023	392
24-07-2023	103
24-06-2023	378
24-05-2023	202
24-04-2023	201
24-03-2023	420
24-02-2023	678
24-01-2023	509

So I will creating an 'Year' filter and two text objects one for Current Year and one for Last Year. If user selects 2024 from Year filter in the Current Year object it has to show sales from Jan 2024 until June 2024 because current month is June and in the Last Year object it has to show Jan 2023 until June 2023.

If User selects 2023 from Year filter in the Current Year object it has to show sales from Jan 2023 until June 2023 and in the Last Year object it has to show blank because we don't have 2022 data.

Next month will be July right, in that case If user selects 2024 from Year filter in the Current Year object it has to show sales from Jan 2024 until July 2024 because current month is going to be July and in the Last Year object it has to show Jan 2023 until July2023.

[marksouzacosta]

Hi @vikasshana, great question.

There are many ways to solve this problem. Let me suggest one.

First, create two variables:

Name: vCurrentDate
Definition: =MakeDate(Max(Year),Month(Today()),Day(Today()))

Name: vPreviousYearDate
Definition: =AddYears('$(vCurrentDate)',-1)

Now, those are your measures:

Current Year:
Sum({<Year = , Date = {">=$(=YearStart(vCurrentDate))<=$(=vCurrentDate)"}>} Sales)

Previous Year:
Sum({<Year = , Date = {">=$(=YearStart(vPreviousYearDate))<=$(=vPreviousYearDate)"}>} Sales)

This is the Load Script I have used:

Sales:
LOAD 
Date(Date#(Date,'DD-MM-YYYY')) AS Date,
Year(Date#(Date,'DD-MM-YYYY')) AS Year,
Num(Sales) AS Sales
INLINE [
Date,Sales
24-12-2024,100
24-11-2024,200
24-10-2024,300
24-09-2024,400
24-08-2024,300
24-07-2024,200
24-06-2024,600
24-05-2024,700
24-04-2024,100
24-03-2024,700
24-02-2024,500
24-01-2024,800
24-12-2023,88
24-11-2023,300
24-10-2023,103
24-09-2023,492
24-08-2023,392
24-07-2023,103
24-06-2023,378
24-05-2023,202
24-04-2023,201
24-03-2023,420
24-02-2023,678
24-01-2023,509
];

Please, let us know if that solves your problem.

Regards,

Mark Costa

[vikasshana]

Thank you @marksouzacosta for the suggestion, it is working if I select 2024 in year filer, but when I select 2023, Last Year and Current Year is showing same values. Requirement is if I select 2023 Last Year has to be blank.

2024 Year Selection

2023 Year Selection

[vikasshana]

@marksouzacosta I've tweaked vPreviousYearDate variable like below and now it is working fine as expected. In order to test I've added one record for 2022.

=MakeDate(Max(Year)-1,Month(Today()),Day(Today()))

INLINE [
Date,Sales
24-01-2022,509

Result

2024 Year Selection

2023 Year Selection

2022 Year Selection