Forum Discussion
Max and Min date
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
10 Replies
pls try this
let Source = #table(type table [Month=date, Order=Int64.Type], {{#date(2023,4,1),1},{#date(2023,5,1),2}}) in Sourcewhat 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
Hi,
What if there are 12 rows (one for each month)? What should the result be?
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"
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
