Thanks Albert, sorry if I am not clear on my question-
I am creating a filter called "Select Month" and I need to select only one month, lets say Jan 2014 from your example then it should return Jan 14 and Dec 13.
1 of 1 people found this helpful
I mocked up a sample data set (attached). I'd start by creating a calculated field which will just give you the first day of each month in your data set. We can use that to populate the parameter.
// Field with just the first of every month, so we can get a unique list of months/years.
Then create your Select Year parameter and choose to populate the values from the Month Year field. Next right-click on the parameter, go to Default Properties, then Date Format and change it to the following:
You'll now have a list like this:
Next create calculated fields that will give you the record ID only if they fall within either the current or prior month:
Current Month Record
// Get Record IDs for current month based on selected month
IF YEAR([Date])=YEAR([Select Month]) AND MONTH([Date])=MONTH([Select Month]) THEN
Previous Month Record
// Get Record IDs for prior month based on selected month
IF YEAR([Date])=YEAR(DATEADD('month',-1,[Select Month])) AND MONTH([Date])=MONTH(DATEADD('month',-1,[Select Month])) THEN
Finally, build a view like this using COUNTD on each of the above calculated measures.
See attached workbook.
Thanks Ken, the logical approach and the way you have implemented and explained in the solution is great.
Thank you. Any time!