I have a data set that contains a start date and and end date for each record, as well as a field for Cost. I want to create a line graph with one line for the cost total for each month in the prior calendar year, and a second line for the cost total for each month this year. Because I want to limit the data displayed in my line graph to records with an end date between January 1 of last year and June 30 of this year; I used a dashboard level filter on End Date. The horizontal axis on my line graph will display the month name for the start date (so each point on my line will be the count of total records with a start date in that month, and an end date in the range I specified).
I have tried two methods for displaying this data:
1. The widget contains a widget filter for records with start dates in the current year.
- The x-axis is Months in Start Date.
- One value is Sum(Cost) ; the other value is PastYear(Sum(Cost)).
- Result: The x-axis only shows months from January to June (expected, since I will have no start dates from this year July forward).
- The first value appears to sum Cost from records with end dates through June of this year (expected)
- The second value appears to sum Cost with records with end dates through June of last year only (was PastYear also applied to the End Date filter?)
2. The second widget contains a widget filter for records with start dates in the prior year.
- The x-axis is Months in Start Date
- One value is Sum(Cost), the other value is Sum (Cost, next([Months in Start Date],12))
- The first value appears to sum Cost by month for each month of the prior year, for records with end dates through June of this year (expected). All 12 months display on the x axis (expected and desirable)
- The second value displays for January through June (expected). The Sum(Cost) does not reconcile to what we would expect for records with a Start Date in those months of this year, with an End Date in the first six months of this year.
If the end date filter is removed from the dashboard, both widgets display the same sum(Cost) by month. We need to filter on End Date because full data will not accumulate for a certain number of months. Do time-related functions shift dates for date filters on a dashboard as well as dates used in building a widget?
