# Getting the same day of the week from a previous year in a Power Query calc column

• ### Question

• Hi Power Query users,

Would like to get your suggestions on this problem. I have a customer that wants to see the previous year's sales for the same day of the week. So today is Friday July 25, 2014. Customer would like to see sales for the closest Friday on the previous year, which was Friday July 26, 2013. Next year on Friday July 26, 2015, customer would see the sales for Friday July 25, 2014 and so on. Customer operates 7 days a week, all days of the year including holidays.

What is the most elegant way to do this? I am assuming the best thing to do is to add this date as a calculated column in the date table, but I am not quite sure the best way to write the formula (including handling of edge cases - leap years, providing null when the previous year is not part of the date table, etc).

I am starting with the date dimension as presented by Matt Masson.

Please help if you have any formula suggestions for this.

Thanks

Friday, July 25, 2014 8:41 PM

• This doesn't make use of Matt's date dimension, but it could be adapted to that. It defines a function Day.DayLastYear which I think will do what you want. It doesn't respect year boundaries, so Day.DayLastYear(#date(2015, 1, 1)) will equal #date(2013, 12, 31) -- but that's probably the right value, right?

let
Day.DayLastYear = (day) =>
let
Previous = Date.AddYears(day, -1),
DayOfWeek = Date.DayOfWeek(day),
PreviousDayOfWeek = Date.DayOfWeek(Previous),
Difference = PreviousDayOfWeek - DayOfWeek,
Adjusted1 = if Difference < -3 then Difference + 7 else Difference,
in
Result
in
Day.DayLastYear(#date(2014, 7, 25))

Saturday, July 26, 2014 1:25 PM

### All replies

• This doesn't make use of Matt's date dimension, but it could be adapted to that. It defines a function Day.DayLastYear which I think will do what you want. It doesn't respect year boundaries, so Day.DayLastYear(#date(2015, 1, 1)) will equal #date(2013, 12, 31) -- but that's probably the right value, right?

let
Day.DayLastYear = (day) =>
let
Previous = Date.AddYears(day, -1),
DayOfWeek = Date.DayOfWeek(day),
PreviousDayOfWeek = Date.DayOfWeek(Previous),
Difference = PreviousDayOfWeek - DayOfWeek,
Adjusted1 = if Difference < -3 then Difference + 7 else Difference,
in
Result
in
Day.DayLastYear(#date(2014, 7, 25))

Saturday, July 26, 2014 1:25 PM
• Thanks for this help Curt. It didn't work out of the box but with a small tweak it worked.

Your function was off by two. For example if I give the date Sunday June 1, 2014, I get back  Friday May 31, 2013.

I noticed the pattern in my first post where the desired resulting date typically looked a day ahead in the year. So I adjusted your first line, added a second line, and I got the results that I wanted.

I put this in the calculated column in my date table and now I can use it in the time based calculations as I wish.

Here is the final function.

Day.DayLastYear = (day) =>
let
PreviousDate = Date.AddYears(day, -1),
Previous = Date.AddDays(PreviousDate, 1),
DayOfWeek = Date.DayOfWeek(day),
PreviousDayOfWeek = Date.DayOfWeek(Previous),
Difference = PreviousDayOfWeek - DayOfWeek,
Adjusted1 = if Difference < -3 then Difference + 7 else Difference,
in
Result

• Marked as answer by Monday, July 28, 2014 9:44 PM
• Unmarked as answer by Tuesday, July 29, 2014 12:05 AM
Monday, July 28, 2014 9:43 PM
• Another possibility is given below:

//Date_PreviousYearSameDayOfWeek Function
/*Get the previous year date on same day of week as current date
If no leap-year is involved, previous year date will land on the same day as current date less 364 days
Otherwise previous year date will land on the same day as current date less 363 days
For year cross-over, "previous year" will actually be beginning of current year, to land on the nearest same day of week*/

(date) =>

//LookupValue Function
//Returns the value in resultColumnList for the row that meets all criteria specified by searchColumnList and searchValue

(resultColumnList,searchColumnList,searchValue)=>
let
SearchValueIndex =    if searchValue <> null then
List.PositionOf(searchColumnList, searchValue)
else null,
ResultColumnValue = if SearchValueIndex <> null and SearchValueIndex <> -1 then
resultColumnList{SearchValueIndex}
else null
in
ResultColumnValue

In the Lookup function, you pass in a list of the current year sales as the first argument, a list of the current year dates as the second argument, and the previous year date in the current row as the third argument. The result is the previous year sales corresponding to the sales on the same days as the current year.

• Edited by Monday, July 28, 2014 10:54 PM added additional details
Monday, July 28, 2014 10:50 PM
• Thanks Colin this looks much better. Your function works and I replaced my calc column with that, much cleaner.

I can't do the lookup though because my data is structured differently.

My data is like this (don't ask)

Date | This Year Net | Prev Year Net

6/1/2014 | 12345 | 43214

6/2/2014 | 123 | 1234

etc

So passing in a list of previous year's sales is problematic. This is why I wanted to just get the date, then take care of looking up the sales value in Power Pivot calc column in the sales data table.

I already figured out the DAX for this (blech, pseudo-code, I simplified out a couple other conditions)

=CALCULATE(

Max([Previous Year Net Sales]),

FILTER(ALL(SalesReports),

[Excel Date]=Date(Year(EARLIER(SalesReports[SameDayOfWeekLastYear]))+1, MONTH(EARLIER(SalesReports[SameDayOfWeekLastYear])), Day(EARLIER(SalesReports[SameDayOfWeekLastYear]))

)

)

Turns out that it was easier to put the SameDayOfWeekLastYear in the Sales table due to the other conditions required for the calculation.

If you have another suggestion for the lookup in this data structure please let me know, especially pushing it back into Power Query and out of the calc column.

Thanks

Tuesday, July 29, 2014 5:31 PM
• Okay, I'm making a couple of assumptions, so please let me know if these assumptions are incorrect:

1) Instead of all sales data being in a single column, current year and last year sales are provided in different columns,

2) The previous year sales column shows the sales occurring on the same date as the current year sales (so in your example, 43214 is the sales figure for 6/1/2013.

If the above assumptions are true, we need to modify how the lookup is done. Let's assume that you created a custom column named SameDayOfWeekLastYear. Create another column called Previous Year Date, using the following function (Date_PreviousYear)

(date) =>
let
PreviousYearDateAdjusted = if (Date.Day(date)=29 and Date.Month(date) = 2) then
#date(9999,1,1)
else PreviousYearDate
in

Incidentally, I created this function the week before your query, for a different purpose. In that case, it was to get the previous year sales occurring on the same date as the current year sales. It actually gets to your starting point, but with all the sales data in a single column to start with. However, when the current year is a leap year and you apply the Date.AddYears function, February 29th is calculated as February 28th of the previous year. If you then do a lookup for previous year sales, you get February 28th sales looked-up twice (the real Feb 28th and the bogus one resulting from taking February 29th back one year). The PreviousYearDateAdjusted identifier puts a truly bogus date instead of a duplicated Feb 28th - one that will always return null when looking up the sales value.

This new date column will then be used to lookup the row of the previous year sales column that corresponds to the dates in the SameDayOfWeekLastYear column.

Assuming that the last step in your query is InsertedCustom1, create another custom column (Previous Year Net_Same Day?), which returns the sales on the same day as the current year (the result that you're after). The formula would be:

LookupValue(InsertedCustom1[Previous Year Net], InsertedCustom1[Previous Year Date], [SameDayOfWeekLastYear])

where LookupValue is the custom function mentioned previously. After creating this new custom column, you can then remove all of the columns you don't need.

Hope that the preceding is somewhat clear.

• Edited by Wednesday, July 30, 2014 3:17 AM added detail
Wednesday, July 30, 2014 3:10 AM
• I know this is a really old topic, but there's an easier way: