Max and Min date

[Anonymous]

Hi,

I am trying to create identify with 1 and 2 the Max and Min date (1=Min and 2=Max).

Table2:

Month          Order

April 2023        1

May 2023         2

Preferly in Power Query 

[Ahmedx]

or try this

let 
     YearNow =  Date.Year( DateTime.LocalNow()),
    MonthNow =  Date.Month( DateTime.LocalNow()),
    date1 =#date(YearNow,MonthNow-2,1) ,
    date2 =#date(YearNow,MonthNow-1,1) ,
    date3=  #date(YearNow,MonthNow,1)  ,
    Source = #table(type table [Month=date, Order=Int64.Type], 
                          {
                              {date1,1},{date2,2},{date3,3}
                               })
in
    Source

[Ashish_Mathur]

Hi,

What if there are 12 rows (one for each month)?  What should the result be?

[Ahmedx]

in that case you can do this:

let
    Source = List.Generate( ()=> #date(2023,1,1),
(x) => x<= #date(2023,12,1),
(x)=> Date.AddMonths(x,1)

),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Month"}, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Order", 1, 1, Int64.Type)
in
    #"Added Index"

[Ahmedx]

pls try this

let
    Source = #table(type table [Month=date, Order=Int64.Type], {{#date(2023,4,1),1},{#date(2023,5,1),2}})
in
    Source

[Anonymous]

In this case month will be dynamic it won't always be those months 

[Ahmedx]

what do you mean dynamic? current month and previous month?

let 
     YearNow =  Date.Year( DateTime.LocalNow()),
      MonthNow =  Date.Month( DateTime.LocalNow()),
    startdate =#date(YearNow,MonthNow-1,1) ,
    EndDate =  #date(YearNow,MonthNow,1)  ,
    Source = #table(type table [Month=date, Order=Int64.Type], 
                          {
                              {startdate,1},{EndDate,2}})
in
    Source

[Anonymous]

This month JUNE 2023 it has APRIL 2023 and MAY 2023, next month

JULY 2023 it will have MAY 2023 and JUNE 2023, next month

AUGUST 2923 it will have JUNE 2023 and JULY 2023... 

It will be always changing 

[Ahmedx]

pls try this

let 
     DateNow =  List.Transform( List.Generate(()=>  Date.AddMonths( Date.From( DateTime.LocalNow()),-2),
        (x) => x <= Date.From( DateTime.LocalNow()),
        (x) =>  Date.AddMonths(x,1)), (x) => Date.StartOfMonth(x)),
    #"Converted to Table" = Table.FromList(DateNow, Splitter.SplitByNothing(), {"Month"}, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Order", 1, 1, Int64.Type)
in
    #"Added Index"

[Anonymous]

It didn't worked I added it in the advanced editor, maybe by doing step by step?

[Ahmedx]

Sample PBIX file attached

Max and Min date.pbix