Power query expression for below excel formula

[Ramendra]

Can someone please help with power query expression for below excel formula.

Where Extract date is the date column and A:A is the same date column in excel.

I need correct expression for Power query only.

I am using custom week dates which start from Wednesday to next Tuesday.

=IF([@[Extract Date]]=MAX(A:A),"Current Week",IF([@[Extract Date]]=(MAX(A:A)-7),"Last Week"))

[amitchandak]

@Ramendra , In power query you have function

Date.IsInCurrentWeek ,Date.IsInPreviousWeek

if Date.IsInCurrentWeek([Date]) then "This Week" else

if Date.IsInPreviousWeek([Date] then "Last Week" else "Other week"

end

refer: Power BI Tutorial Series for Beginners: Date.IsInCurrentWeek, Date.IsInNextWeek, Date.IsInPreviousWeek: https://www.youtube.com/watch?v=HgrG9I51Nbk 

[Ramendra]

Hi @amitchandak ,

Sorry but I am using custom week which start from Wednesday to next Tuesday

But this formula will take standard week only.
Please correct me if I am wrong?

[v-easonf-msft · Accepted solution]

Hi,  @Ramendra 

Use  Date.AddDays([Extract Date],-3)  to replace [Extract Date].

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc6xEYAgEADBXoidgT8EoRaG/tvATC/cbNdKFCKPHKR9farSLTWpS480pPkXRQpJF3RBF3RBF3RBF97LPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Extract Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Extract Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Date.IsInCurrentWeek(Date.AddDays([Extract Date],-3)) then "This Week" else if Date.IsInPreviousWeek(Date.AddDays([Extract Date],-3)) then "Last Week" else "Other week")
in
    #"Added Custom"

Best Regards,
Community Support Team _ Eason

 

[Ramendra]

@v-easonf-msft : Thanks for help.