Date Formula Support Needed

[Bennets04]

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! 🙂

[dax]

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

[dax]

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