Forum Discussion

fabric-excel's avatar
fabric-excel
Frequent Visitor
1 year ago
Solved

Second Max Date based on the current Row Date

Hello,   I'm current using M Language from Power Query to achieve this result (I developed already this code with DAX for Power BI and also with Excel formulas, but I need to create that logic with...
Show and Tell
Tips and Tricks
Tutorial Requests
  • dufoq3's avatar
    dufoq3
    1 year ago

    Hi fabric-excel, check this:

     

    Output

    let
    Source = Table.FromList({#date(2020,3,1), #date(2020,3,7), #date(2020,3,5), #date(2020,10,8), #date(2019,11,30), #date(2020,3,1)}, Splitter.SplitByNothing(), type table[Date=date]),
    Dates = List.Buffer(List.Sort(Source[Date])),
    StepBack = Source,
    Ad_Previous = Table.AddColumn(StepBack, "Previous", each try Dates{List.PositionOf(Dates, [Date])-1} otherwise null, type date)
in
    Ad_Previous
dufoq3's avatar
dufoq3
Community Champion
1 year ago

Hi fabric-excel, check this:

 

Output

let
    Source = Table.FromList({#date(2020,3,1), #date(2020,3,7), #date(2020,3,5), #date(2020,10,8), #date(2019,11,30), #date(2020,3,1)}, Splitter.SplitByNothing(), type table[Date=date]),
    Dates = List.Buffer(List.Sort(Source[Date])),
    StepBack = Source,
    Ad_Previous = Table.AddColumn(StepBack, "Previous", each try Dates{List.PositionOf(Dates, [Date])-1} otherwise null, type date)
in
    Ad_Previous