1 Reply Latest reply on Aug 20, 2018 4:14 AM by Okechukwu Ossai

# How can I calculate comparison of the period to max of selected year and the last year

Hi, everyone. help me for making a calculate field.

I've created two parameters. one shows year, the other shows month.

If I click on January 2017, compared with January 2016.

If you select all of the month parameter, you will need to compare the sum from January ~ June 2017 to January ~ June 2016.

because the maximum value for 2017 is April.

How can I this problem?

I've attached Sample dashboard. pliz help me.

• ###### 1. Re: How can I calculate comparison of the period to max of selected year and the last year

Hi Jiwon,

I've modified the Sample Superstore dataset so that Year 2017 ends in April.

Step 1: Correct the typographical error in your Year parameter. Change 207 to 2017.

Step 2: Create a calculated field [Minimum Month]

MIN({FIXED : MAX(IF YEAR([Order Date]) = INT([Parameters].[Year]) THEN DATEPART('month', [Order Date]) END)},

{FIXED : MAX(IF YEAR([Order Date]) = INT([Parameters].[Year])-1 THEN DATEPART('month', [Order Date]) END)})

Step 3: Create a calculated field [Date Filter]

IF [Month] <> 'All' THEN

IF YEAR([Order Date]) = INT([Parameters].[Year]) OR YEAR([Order Date]) = INT([Parameters].[Year])-1 THEN

IF DATEPART('month',[Order Date]) = INT([Month]) THEN 1

ELSE 0 END

ELSE 0 END

ELSE

IF YEAR([Order Date]) = INT([Parameters].[Year]) OR YEAR([Order Date]) = INT([Parameters].[Year])-1 THEN

IF DATEPART('month',[Order Date]) <= [Minimum Month] THEN 1

ELSE 0 END

ELSE 0 END

END

Right click on the field name and select convert to discrete. Add [Date Filter] to the filter shelf and set to '1'.

Hope this helps.

Ossai