none
Getting the same day of the week from a previous year in a Power Query calc column RRS feed

  • 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

Answers

  • 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,
                Adjusted2 = if Adjusted1 > 3 then Adjusted1 - 7 else Adjusted1,
                Result = Date.AddDays(Previous, Adjusted2)
            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,
                Adjusted2 = if Adjusted1 > 3 then Adjusted1 - 7 else Adjusted1,
                Result = Date.AddDays(Previous, Adjusted2)
            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,
                Adjusted2 = if Adjusted1 > 3 then Adjusted1 - 7 else Adjusted1,
                Result = Date.AddDays(Previous, Adjusted2)
            in
                Result


    • Marked as answer by Cathy Dumas - Monday, July 28, 2014 9:44 PM
    • Unmarked as answer by Cathy Dumas - 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) =>
        if Date.DayOfWeek(Date.AddDays(date,-364))=Date.DayOfWeek(date) then Date.AddDays(date,-364)
        else Date.AddDays(date,-363)
       
    //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 Colin Banfield 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   
        PreviousYearDate = Date.AddYears(date,-1),
        PreviousYearDateAdjusted = if (Date.Day(date)=29 and Date.Month(date) = 2) then
                                       #date(9999,1,1)
                                   else PreviousYearDate
    in
        PreviousYearDateAdjusted

    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 Colin Banfield 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:

    SameDayLastYear = Date.AddWeeks([YourDateField],-52)


    Sunday, March 6, 2016 3:19 PM
  • Some years have 53 weeks ;)
    Wednesday, July 22, 2020 10:46 AM