PowerBi or Power Query Formula for dates before previous date

[Honne2021]

The Effective End Date is being done manually which is grueling cause there are so many dates in our file now. Is there a dax formula or m code that I can use to get the Effective End Date? The effective end date is the day before the new effective date (not sure if I explained that well enough but here's a picture). 



Thank you in advance! 

[Anonymous · Accepted solution]

Hi @Honne2021 ,

Got it. In this case for quick implementation, I'd suggest you use DAX:

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

Best Regards,
Eyelyn Qin

[Honne2021]

Thank you very much!

[Anonymous]

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.

[Honne2021]

Hi, sorry I forgot to mention that the column can refer to different stores. The Effective End Date should be specific to the particular store only and not the entire Effective Date column. 
 

[Vijay_A_Verma]

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"

[Vijay_A_Verma]

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"

[Honne2021]

If it is an excel file, should I still keep the "Compression.Deflate" code?