PowerBi or Power Query Formula for dates before previous date

Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc2xDcAgDATAXVxHet5YOMxisf8awVBAyqsuQhyV0MIm4wnpqOWoQS31LlGhP5GHBk/0BYfd2MHU+AA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Effective End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Effective End Date", type date}}),
    #"Get Effective Date" = Table.FromColumns(Table.ToColumns(#"Changed Type") & {{null} & List.RemoveLastN(#"Changed Type"[Effective End Date],1)},Table.ColumnNames(#"Changed Type")&{"Effective Date"})
in
    #"Get Effective Date"

Right way would be to import the file in PQ through Excel - Advanced Editor and delete everything after Source and copy everything after source from here.  (Make sure comma is there at the end Source statement). Hence, in Excel this would become like this (so you need to change only Table1 here)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Effective End Date", type date}}),
    #"Get Effective Date" = Table.FromColumns(Table.ToColumns(#"Changed Type") & {{null} & List.RemoveLastN(#"Changed Type"[Effective End Date],1)},Table.ColumnNames(#"Changed Type")&{"Effective Date"})
in
    #"Get Effective Date"

Hi Honne2021 ,

According to this—— Is there a dax formula or m code that I can use to get the Effective End Date? 

I have done it in this two ways, and both need an index column in Power Query.

1.Whole M syntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNNU31TdTitUBc8z0LfQNERxDAwTPQt9M38hUKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Effective Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Effective Date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Next Row", each try #"Added Index" [Effective Date]{ [Index] }otherwise null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Effective End Date(M)", each Date.AddDays([Next Row] ,-1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Next Row"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Effective End Date(M)", type date}})
in
    #"Changed Type1"

2. Using DAX to create a column:

Effective End Date(DAX) = 
var _next= CALCULATE(MAX('Table'[Effective Date]),FILTER('Table',[Index]=EARLIER('Table'[Index])+1))
return IF(_next=BLANK(),BLANK(), _next-1)

Final output:

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