Forum Discussion
Solved
PowerBi or Power Query Formula for dates before previous date
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 effec...
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
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.
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.
