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

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"