Forum Discussion
Previous Year Total With Non-Continuous Dates
Hello,
I have a barchart that plots data over a period of time. Users are able to select and multiselect individual bars to filter the dashboard to just the months that are selected from the bar chart.
I would like to display the prior year total for the same period of the previous year. The following calculation is working great when single or multiple Continuous months are selected:
LY = CALCULATE(SUM(SALES),FILTER(SALES_TABLE, SALES_TABLE[DATE] IN (DATEADD(VALUES(CALENDARTABLE[DATE],-1,YEAR)))
When a user selects non-Continuous months, like March, June, September. The calculation raises an error that DATEADD requires continious dates. I've tried wrapping the calculation in IFERROR and just returning something else to move past this, but that does not work either.
I have the following questions:
1.) Is there a calculation I can use to accurately aggregate the previous years result of multiple non-continious dates?
2.) If #1 is not possible, is it possible to somehow suppress this error and return a static value when this is encountered? Worst case I can add a tooltip that calls out the limitation of the calculation and display a placeholder value instead.
See below.
9 Replies
- davehus
Memorable Member
Hi Anonymous
Try
CALCULATE([Sales],SAMEPERIODLASTYEAR('Date Table'[DateKey]))- AnonymousNot applicable
"SAMEPERIODLASTYEAR expects a contigious selection when the date column comes from a table on the 1-side of a bi-directional relationship."
I've tried swapping the relationships from Calendar-> Sales, single.
Sales -> Calendar, single.
Sales <-> Calendar, both.
Same error each time.
- davehus
Memorable Member
Are you passing the date from the fact table or the date from your date table into the measure. It should be the date table.
If the date table are the dates continuos as in 1st January to 31st December with no missing date values?
Contgious means in sequence.