none
Power Query Previous day calculation RRS feed

  • Question

  • Hi all,

    I want to ask if there is a way to create a previous day formula in power query. I am using Power BI for it and I am kind of new in the language. 

    So far I tried with if statements and date.adddays, but it doesn't work. 

    My goal is to create a calculated column that has the values from the previous day so I can subtract them. 

    My data model is simple:

    We also have some repeated dates. But I don't think that should be an issue. 

    Best and thanks,

    Jorge



    • Edited by Jmenas Monday, May 15, 2017 9:13 AM
    Monday, May 15, 2017 9:08 AM

Answers

  • For this type of calculation, I use a LookupValue custom function, which is designed to emulate DAX's LOOKUPVALUE function. However, to handle duplicate dates, you would have to group your sales by date first. In addition, you may need to sort the table by ascending dates to guarantee the date order.

    LookupValue:

    (resultColumnList as list, searchColumnList as list, searchValue)=>
    let
        SearchValueIndex = List.PositionOf(searchColumnList, searchValue),
        ResultColumnValue = try resultColumnList{SearchValueIndex} otherwise null
    in
        ResultColumnValue

    You can paste the above code into a blank query and name the query LookupValue.

    Then you need to add one step in your main query:

    AddedCustom = Table.AddColumn(PreviousStep, "previous day sales", each LookupValue(PreviousStep[sales], PreviousStep[date],Date.AddDays([date], -1)))

    If you don't expect to ever reuse the LookupValue function in another project, then you can use Anthony's solution, or embed the LookupValue code in the AddedCustom step i.e.

    AddedCustom = Table.AddColumn(
    PreviousStep,
    "previous day sales",
    each try PreviousStep[sales]{List.PositionOf(PreviousStep[date],Date.AddDays([date], -1)}
    otherwise null
    )





    Tuesday, May 16, 2017 1:14 PM

All replies

  • Assuming your excel data is in "Table1" then here is your query

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChType = Table.TransformColumnTypes(Source,{{"date", type date}, {"cost", Currency.Type}, {"sales", Currency.Type}, {"id", type text}}),
        AddPreviousDay = Table.AddColumn(ChType, "Previous day", each Date.AddDays( Date.From([date]), -1 ), type date ),
        MergeSelf = Table.NestedJoin(AddPreviousDay,{"date"},AddPreviousDay,{"Previous day"},"NewColumn",JoinKind.LeftOuter),
        ExpandSales = Table.ExpandTableColumn(MergeSelf, "NewColumn", {"sales"}, {"sales Prior Day"})
    in
        ExpandSales

    If you have duplicated dates, you could first group them by Date using "Group by"


    Usefull M Formula:

    Previous day from Current day:

    = Date.AddDays(Date.From( DateTime.FixedLocalNow() ), -1 )

    Previous day from your Date Column:

    = Table.AddColumn(PreviousStep, "Previous Day", each Date.AddDays( Date.From( [Date]), -1 ))

    Hope it helps,
    Anthony

    • Edited by anthony34 Tuesday, May 16, 2017 10:46 AM
    • Proposed as answer by anthony34 Tuesday, May 16, 2017 11:06 AM
    • Unproposed as answer by anthony34 Wednesday, May 31, 2017 7:17 PM
    Tuesday, May 16, 2017 10:38 AM
  • For this type of calculation, I use a LookupValue custom function, which is designed to emulate DAX's LOOKUPVALUE function. However, to handle duplicate dates, you would have to group your sales by date first. In addition, you may need to sort the table by ascending dates to guarantee the date order.

    LookupValue:

    (resultColumnList as list, searchColumnList as list, searchValue)=>
    let
        SearchValueIndex = List.PositionOf(searchColumnList, searchValue),
        ResultColumnValue = try resultColumnList{SearchValueIndex} otherwise null
    in
        ResultColumnValue

    You can paste the above code into a blank query and name the query LookupValue.

    Then you need to add one step in your main query:

    AddedCustom = Table.AddColumn(PreviousStep, "previous day sales", each LookupValue(PreviousStep[sales], PreviousStep[date],Date.AddDays([date], -1)))

    If you don't expect to ever reuse the LookupValue function in another project, then you can use Anthony's solution, or embed the LookupValue code in the AddedCustom step i.e.

    AddedCustom = Table.AddColumn(
    PreviousStep,
    "previous day sales",
    each try PreviousStep[sales]{List.PositionOf(PreviousStep[date],Date.AddDays([date], -1)}
    otherwise null
    )





    Tuesday, May 16, 2017 1:14 PM