Please see the attached workbook. I've created a parameter that allows the user to select a month/year and then I created a calculated field that references this value. I've made it so that the calculated field is a filter and it returns any data that's within 12 months of the parameter date, but not greater than the date. Hence if you have data for 2010-2015 the end user would select a date in 2014 and would not be burdened with the extra data points after their chosen time.
Last12Months.twbx 1.2 MB
1) Create a Calculated Fields as below to get the Months
Syntax: month([Order Date])
2) Create a Calculated Fields as below to get the Years
Syntax: year([Order Date])
3) Create parameters for both Month, Year.
4) Then make a date as per selections of Parameters
Name: Selected Date
Synntax: date("01" +"/" str([Month Parameter] +"/" str([Year Parameter]))
5) Then create a calculated field as below,
Name: Sales - Selected Period
Synyax: if datediff('month', datetrunc('month',[Order Date], [Selected Date])>=0 and datediff('month', datetrunc('month',[Order Date], [Selected Date])<=12 then [Sales] end
6) Now drag the [Order Date], [Sales - Selected Period] fields.
7) Drag the [Sales - Selected Period] onto filters shelf then select "Sum" then select "Special" tab then select "Non-null values" option.
8) Now you will ge the Data for last 12 months of selected Year, Month values from Parameter.
Could you explain what this field does, date("01" +"/" str([Month Parameter] +"/" str([Year Parameter]))
also this is not getting validated
We are creating a date from Year, Month Values.
EX: Year = 2012, Month =3 then it will give 1st Mar 2012
I have tested that calculated field before share the solution, and it works well.
both my month and year parameters i created already as string data type, is there anyway of doing this?