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.
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
