Forum Discussion

Honne2021's avatar
Honne2021
Helper II
4 years ago
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...
General Comment
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

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's avatar
Honne2021
Helper II
4 years ago

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