Special sameperiodlastyear

[Chapin4u]

Hi, I'm working on a report and using this measure:

US$ LY = CALCULATE([US$],SAMEPERIODLASTYEAR('Calendar'[Date]))

As it's now, works fine and have a slicer for date selecting  7/15/2017 that was a Saturday, so the visual for US$ it shows the amount for that date and for the visual US$ LY it shows the amount for 7/15/2016 that was a Friday. 

What it's needed:

 Need to compare the 7/15/2017 that was a Saturday with 7/16/2016 that was a Saturday also. So for any date selected, I must match the week day name of the date selected with the week day name of the previous year. The idea is to compare the sales of Saturday the 15th of this year with the Saturday 16th of 2016.

Hoping I have explained enough and clearly, so my request for how to the comparison is received. Thanks.

[KHorseman]

Will this always be comparing a single date to a single equivalent day the previous year, or will it sometimes be used on periods of multiple days?

[Chapin4u]

Yes, no multiple dates, it will always be a single date.

[KHorseman]

I believe this should do it. I haven't actually tested yet, but...

US$ LY = VAR basedate = FIRSTDATE('Calendar'[Date])
RETURN CALCULATE(
	[US$],
	FILTER(
		ALL('Calendar'),
		YEAR('Calendar'[Date]) = YEAR(basedate) - 1 &&
		WEEKNUM('Calendar'[Date]) = WEEKNUM(basedate) &&
		WEEKDAY('Calendar'[Date]) = WEEKDAY(basedate)
	)
)

If you do put this against multiple dates, it will only return the last year value for the first day in the range.

[Chapin4u]

Thanks, I tested accordingly as you wrote the measure but unfortunately didn't work,

[KHorseman]

I'm not sure what to do with that without some details.

[Chapin4u]

Thanks, I tested accordingly as you wrote the measure but unfortunately didn't work,

[KHorseman]

What results did you get and what was wrong with them?

[Chapin4u]

I compared two table visuals, one table has the date and US$ for July 2017 and the other table with the same columns for July 2016. Also have 2 card visuals, 1  with US$ and the other for US$ LY, the US$ LY does not match with any date and US$ for the month of July 2016

[KHorseman]

Oh you know what, I wasn't thinking of individual months. I was thinking of weeks of the year. I don't think you can map it to individual months because you won't even have the same number of Saturdays in a month some years. I just went by week of the year, so it's the same Saturday relative to the year.

[KHorseman]

Here, maybe this will help illustrate the problem with the way you want to do it. You have to go by week of the year because Saturdays don't map to individual months. Here's a 3 year spread. If I want to compare week 5 of January 2017 to Week 5 of January 2016 that's not a problem. But what if I want to compare the next week? Week 1 of February 2017 should map to Week 1 of February 2016 right? But what happens to Week 6 of January 2016? Do we just discard that week? Later in the year it gets even worse.

So I think you should be comparing Saturdays by week of the year, not by month. So that July week you were looking at should probably be compared to the last Saturday in June and you'll find the correct results.

[Chapin4u]

Thanks for the clear illustration.

[v-ljerr-msft]

Hi @Chapin4u,

Do you still have any question on this issue? If not, could you accept the corresponding reply as solution to close this thread? 

Regards

[Chapin4u · Accepted solution]

Hi,

I solved my question using this:

Date LY =
VAR DeductYear = DATEADD('Calendar'[Date], -1, YEAR)
VAR ShiftDays = SWITCH(INT('Calendar'[Date] - DeductYear), 365, 1, 366, 2)
RETURN DeductYear + ShiftDays

[Chapin4u]

Let me test it, tks

[KHorseman]

Cool. Let me know if it works.