Previous Year Total With Non-Continuous Dates

[eradcliffe]

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.     

[davehus]

Hi @eradcliffe 

Try 

CALCULATE([Sales],SAMEPERIODLASTYEAR('Date Table'[DateKey]))

[eradcliffe]

"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]

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.

[eradcliffe]

The dates in the chart are driven from the CalendarTable. I'm saying users are able to select non-continious dates from the chart (CalendarTable) and the rest of the measures on the dashboard aggregates based on what a user selects. If you select Janaury-2021 and April-2021, it aggregates the sales from those two years, that works flawlessly for multiple measures. The problem is getting the previous year aggregation when a user selects non-continious months, like January and April.

I don't want the previous year aggregation of sales BETWEEN January and April, I want the aggregated previous year sales of January and April.    

[davehus]

I understand that, however the potential reason that it is not working is that there is non continuos rows somewhere. See example from my dashboard that is using Sameperiodlastyear

example supplied.

[eradcliffe]

What is the relationship configure between the sales table and the calendartable?

[davehus · Accepted solution]

See below.

[eradcliffe]

Looks like my relationship as the problem. Thank you!

[davehus]

No problem, Glad you got sorted. 🙂