Yesterday Last Year

[cmcreynolds]

Okay, my issue here is that there are several ways to do this. We have data that comes through every day and he asked me this morning for "yesterday's totals and comparison to same day last year"

So, my first inclination was to, within PowerPivot, take TODAY()-364. which would probably give a "close enough" response. BUT, it would not be exact when we have leap years like this year.

Plus, I think he wants the report to compare (for example) 2/16/2016 with 2/16/2015. So, is this accurate:

CALCULATE(........, (filter#1) YEAR([contactdate])=YEAR(TODAY())-1, (filter#2) and this is the weird part MONTH([contactdate])=MONTH(TODAY()-1), (filter #3) DAY????([contactdate])=DAY????(TODAY()-1)))

Is it necessary to use three filters to do this? I'm not sure if I was overthinking it.

 

[PentaGalCXO]

how about this:

DayTotalPriorYear:=calculate(<measure that totals>,filter(datedim,datedim[date]=date(year(today()-1)-1,month(today()-1),day(today()-1))))

Side note - I hardly ever use today() in a data model. Almost always the calculations are based on the age of the dataset. Typically I have a measure that determines the date of the youngest data point:
LatestTransactionDate:=calculate(max(Transactions[TransDate]),all(Transactions))
Then I would use [LatestTransactionDate] in my DAX equations instead of today(). This gives consistency to your workbooks so they can be viewed/retested/audited over time.

 

[baitmaster]

You might be able to simplify this request. If you are comparing data with the "same day" last year are you looking to compare actual dates (29 Feb won't exist last year so what you gonna do?) or weekdays? In my experience it would be weekdays, e.g. you wouldn't compare sales figures from Monday and Sunday just because they have the same date. So it might be prudent to just compare 52*7 days ago, rather than mess around with recreating consistent dates

 

[cmcreynolds]

The February 29 issue is exactly what forced me into this question. We have data coming in 24/7, and it's always been that way. While I completely agree that Mondays should be compared to Mondays, this is specifically what he asked for. (*in other words, time to ignore any analyst's suggestions*)

@PentaGal - I basically used your same formula. I just get kind of nervous when I add the third filter

But thank you for your help!

 

[PentaGalCXO]

For those curious how the above formula treats a leap day:
:=date(year("2/29/2016")-1,month("2/29/2016"),day("2/29/2016"))
results: 3/1/2015