Dates Clarification

[pigsinblankets]

Good Afternoon, 

I have a date column and I want to change values in another column based on when the new financial year starts.

I just want to clarify would the below method be the correct Syntax/approach for this to say if the date is greater than 31st March I.E 1st April it would trigger it from 1st April 2023?

each [Year],
each if [Max Update Date]>31/03/2023 then "2023/24 Delivered to Date" else [Year],
Replacer.ReplaceText,{"Year"})

[v-xiaotang · Accepted solution]

Hi @pigsinblankets 

here are some information for your reference

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQHQktLM6VYHRDHCMExgsiY4+RYgDnGcE4sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"1996","2005",Replacer.ReplaceText,{"date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"date", type date}})
in
    #"Changed Type"

Solved: Change years in a Date column in Query Editor - Microsoft Power BI Community

=if [Release date] < #date(2020, 1, 1) then  1
      else if [Release date] > #date(2019, 12, 31) and [Release date] < #date(2021, 1, 1) then 2
      else if [closed date] < #date(2021, 1, 1) then 3
      else  0

Solved: Power query - if function with dates - Microsoft Power BI Community

Best Regards,

Community Support Team _Tang

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

[pigsinblankets]

Thanks so much I have this working now was struggling to find the appropriate historical forum thread! 🙂