Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
third_hicana
Helper IV
Helper IV

Get the next latest Date after the Max Date in Power Query

Hi. Asking for you help how can I create a custom column in power query where it will get the next latest date after the max date in a Date column. For example, in the column below the max date is 17/04/2024 then the next latest date is 27/03/2024. I want to get the 27/03/2024 in a created column. 

third_hicana_0-1722508782619.png

 

Thank you very much for your help.

 

-Third

1 ACCEPTED SOLUTION
bhanu_gautam
Super User
Super User

@third_hicana ,Go to the Add Column tab.
Click on Custom Column.
Use the following formula to get the next latest date:

let
    // Load your data
    Source = YourDataSource,
   
    // Sort the date column in descending order
    SortedTable = Table.Sort(Source,{{"Date", Order.Descending}}),
   
    // Add an index column
    IndexedTable = Table.AddIndexColumn(SortedTable, "Index", 1, 1, Int64.Type),
   
    // Get the max date
    MaxDate = List.Max(IndexedTable[Date]),
   
    // Filter out the max date
    FilteredTable = Table.SelectRows(IndexedTable, each [Date] <> MaxDate),
   
    // Get the next latest date
    NextLatestDate = FilteredTable{0}[Date],
   
    // Add a custom column with the next latest date
    AddedCustomColumn = Table.AddColumn(IndexedTable, "NextLatestDate", each if [Date] = MaxDate then NextLatestDate else null)
in
    AddedCustomColumn



Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






View solution in original post

2 REPLIES 2
third_hicana
Helper IV
Helper IV

@bhanu_gautam  Than you very much for your help.

bhanu_gautam
Super User
Super User

@third_hicana ,Go to the Add Column tab.
Click on Custom Column.
Use the following formula to get the next latest date:

let
    // Load your data
    Source = YourDataSource,
   
    // Sort the date column in descending order
    SortedTable = Table.Sort(Source,{{"Date", Order.Descending}}),
   
    // Add an index column
    IndexedTable = Table.AddIndexColumn(SortedTable, "Index", 1, 1, Int64.Type),
   
    // Get the max date
    MaxDate = List.Max(IndexedTable[Date]),
   
    // Filter out the max date
    FilteredTable = Table.SelectRows(IndexedTable, each [Date] <> MaxDate),
   
    // Get the next latest date
    NextLatestDate = FilteredTable{0}[Date],
   
    // Add a custom column with the next latest date
    AddedCustomColumn = Table.AddColumn(IndexedTable, "NextLatestDate", each if [Date] = MaxDate then NextLatestDate else null)
in
    AddedCustomColumn



Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.