Forum Discussion

Bennets04's avatar
Bennets04
Regular Visitor
7 years ago

Date Formula Support Needed

Good afternoon all,

 

I'm looking for some help with a BI Power Query formula (Trying to cut out lots of manual work) that will take a date from a column (So 10/07/2019) and return back whether that date was Last Week, Last Week -1, Last Week -2, etc up to Last Week -4.

 

With the data, it refers to I want to show this in a week format and feel those headings would be better visually

 

Any help or support would be incredible! :)

Need Help

2 Replies

  • dax's avatar
    dax
    Community Support
    7 years ago

    Hi Bemmets04,

    If you want to create condition column by power query, you could try below to see whether it works or not

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ31ze0UIrVQfAMkHmmKFIIjpm+kSkyz9gAmQeUigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if(Date.IsInCurrentWeek([date])) then "this week" else if (Date.IsInPreviousNWeeks([date],1)) then "last week" else if (Date.IsInPreviousNWeeks([date],2)) then "laset week -1" else if (Date.IsInPreviousNWeeks([date],3)) then "laset week -2" else "other")
   
in
   #"Added Custom"

    Best Regards,

    Zoe Zhi

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • dax's avatar
    dax
    Community Support
    7 years ago

    Hi 

     

    Did this help you solve your issue? If so and if you'd like to, you could mark corresponding post as answer or share your solutions. That way, people who in this forum and have similar issue will benefit from it.

     

    Best Regards,
    Zoe Zhi