It seems that many people are struggling to accomplish this. I'm trying to show a comparison in a table with this year's sales, and last year over the same time. This year is simple, but last year is proving to be difficult because my sales table doesn't have continguous dates, so I can't use SAMEPERIODLASTYEAR.
I have created the following calcualtion which correctly shows the total in the previous year through the current month, but shows the entire month, not to-date:
PY YTD Net Sales =
CALCULATE(
SUM(Sales[NET SALES]),
FILTER(
ALL('Date2'),
Date2[YEAR] = (YEAR(NOW())-1)
&& Date2[MONTH NUMBER] <= MONTH(NOW())
&& Date2[DAY] <= MAX(Sales[Day])
)
)
As you can see, I have a table created with continguous dates, 'date2', and have a relationship establish between this table, and my sales table (one to many relationship).
