help with advanced power query

[sathishramuk33]

hi all ,

 i create one query , in this query i fix to maxium date is today date, but now  i need to add on next year date .so how to add on the 2022 year date in this query .

my query is :

let
// Data is never the current day but always one day behind
TodaysDate = Date.From(DateTimeZone.FixedLocalNow()), // TodaysDate = #"MaxDateForPeriodTable"

LastYearText = Number.ToText(Date.Year(Date.AddYears(Date.From(Date.StartOfYear(TodaysDate)),-1))),
FiscalStartMonth = Date.Month(Date.FromText(""&LastYearText&"-"& Number.ToText(#"Fiscal Start Month")&"-01"&"")),
FiscalStartMonthDay = Date.Day(Date.StartOfMonth(Date.FromText(""&LastYearText&"-"& Number.ToText(#"Fiscal Start Month")&"-01"&""))),
FiscalEndMonth = Date.Month(Date.FromText(""&LastYearText&"-"&Number.ToText(#"Fiscal End Month")&"-01"&"")),
FiscalEndMonthDay = Date.Day(Date.EndOfMonth(Date.FromText(""&LastYearText&"-"&Number.ToText(#"Fiscal End Month")&"-01"&""))),
// FiscalStartMonth = Date.Month(Date.FromText(""&LastYearText&"-"& #"Fiscal Start Month"&"-01"&"")),
// FiscalStartMonthDay = Date.Day(Date.StartOfMonth(Date.FromText(""&LastYearText&"-"& #"Fiscal Start Month"&"-01"&""))),
// FiscalEndMonth = Date.Month(Date.FromText(""&LastYearText&"-"&#"Fiscal End Month"&"-01"&"")),
// FiscalEndMonthDay = Date.Day(Date.EndOfMonth(Date.FromText(""&LastYearText&"-"&#"Fiscal End Month"&"-01"&""))),
Ranges = {

{"Month to Date",
Date.From(Date.StartOfMonth(TodaysDate)),
TodaysDate,
1,
"Calendar"},

{"MTD",
Date.From(Date.StartOfMonth(TodaysDate)),
TodaysDate,
1,
"Calendar"},

{"Previous MTD",
Date.AddMonths(Date.From(Date.StartOfMonth(TodaysDate)),-1),
Date.AddMonths(TodaysDate,-1),
1,
"Calendar"},

// {"Prev Year MTD",
// Date.AddYears(Date.From(Date.StartOfMonth(TodaysDate)),-1),
// Date.AddYears(TodaysDate,-1),
// 2,
// "Calendar"},

{"YTD",
Date.From(Date.StartOfYear(TodaysDate)),
TodaysDate,
3,
"Calendar"},

{"Previous YTD",
Date.AddYears(Date.From(Date.StartOfYear(TodaysDate)),-1),
Date.AddYears(Date.From(TodaysDate),-1),
4,
"Calendar"},

{"FY-YTD",
if Date.Month(TodaysDate) < FiscalStartMonth then #date(Date.Year(Date.AddYears(TodaysDate,-1)),FiscalStartMonth,FiscalStartMonthDay) else #date(Date.Year(TodaysDate),FiscalStartMonth ,FiscalStartMonthDay),
TodaysDate,
5,
"Fiscal"},

// {"FY-LY YTD",
// if Date.Month(TodaysDate) < FiscalStartMonth then #date(Date.Year(Date.AddYears(TodaysDate,-2)),FiscalStartMonth ,FiscalStartMonthDay) else #date(Date.Year(Date.AddYears(TodaysDate,-1)),FiscalStartMonth ,FiscalStartMonthDay),
// Date.AddYears(TodaysDate,-1),
// 6,
// "Fiscal"},

// {"FY-LY",
// if Date.Month(TodaysDate) < FiscalStartMonth then #date(Date.Year(Date.AddYears(TodaysDate,-2)),FiscalStartMonth ,FiscalStartMonthDay) else #date(Date.Year(Date.AddYears(TodaysDate,-1)),FiscalStartMonth ,FiscalStartMonthDay),
// if Date.Month(TodaysDate) < FiscalStartMonth then #date(Date.Year(Date.AddYears(TodaysDate,-1)),FiscalEndMonth ,FiscalEndMonthDay) else #date(Date.Year(Date.AddYears(TodaysDate,+0)),FiscalEndMonth ,FiscalEndMonthDay),
// 6,
// "Fiscal"},

// {"QTD",
// Date.AddQuarters(Date.StartOfQuarter(TodaysDate),+0),
// Date.AddQuarters(TodaysDate,+0),
// 9,
// "Calendar"},

// {"Prev Q",
// Date.AddQuarters(Date.StartOfQuarter(TodaysDate),-1),
// Date.AddQuarters(Date.EndOfQuarter(TodaysDate),-1),
// 9,
// "Calendar"},

// {"Prev QTD LY",
// Date.AddYears(Date.AddQuarters(Date.StartOfQuarter(TodaysDate),+0),-1),
// Date.AddYears(Date.AddQuarters(Date.EndOfQuarter(TodaysDate),+0),-1),
// 10,
// "Calendar"},

// {"Previous Year",
// Date.AddYears(Date.From(Date.StartOfYear(TodaysDate)),-1),
// Date.AddYears(Date.From(Date.EndOfYear(TodaysDate)),-1),
// 11,
// "Calendar"},

{"Previous Month",
Date.StartOfMonth(Date.AddMonths((TodaysDate),-1)),
Date.EndOfMonth(Date.AddMonths((TodaysDate),-1)),
12,
"Calendar"},

// {"Current Month",
// Date.From(Date.StartOfMonth(TodaysDate)),
// TodaysDate,
// 7,
// "Fiscal"},

// {"Previous Quarter",
// Date.AddQuarters(Date.StartOfQuarter(TodaysDate),-1),
// Date.AddQuarters(Date.EndOfQuarter(TodaysDate),-1),
// 13,
// "Calendar"},

// {"Current Quarter",
// Date.StartOfQuarter(TodaysDate),
// Date.EndOfQuarter(TodaysDate),
// 8,
// "Fiscal"},

{"Rolling 12 Months",
Date.AddMonths(TodaysDate,-12) + #duration(0,0,0,0),
TodaysDate,
14,
"Calendar"},

{"Rolling 7 Days",
Date.AddDays(TodaysDate,-7) + #duration(0,0,0,0),
TodaysDate,
//Date.AddDays(TodaysDate,-1),
15,
"Calendar"},

{"Rolling 14 Days",
Date.AddDays(TodaysDate,-14) + #duration(0,0,0,0),
TodaysDate,
15,
"Calendar"},

{"Rolling 30 Days",
Date.AddDays(TodaysDate,-30) + #duration(0,0,0,0),
TodaysDate,
21,
"Calendar"},

{"Rolling 60 Days",
Date.AddDays(TodaysDate,-60) + #duration(0,0,0,0),
TodaysDate,
21,
"Calendar"},

{"Previous 6 Months",
Date.StartOfMonth(Date.AddMonths(TodaysDate,-5)) ,
TodaysDate,
19,
"Calendar"},

{"Month Estimate",
Date.StartOfMonth(Date.AddYears(Date.AddMonths(TodaysDate,-1),-1)) ,
Date.EndOfMonth(Date.AddMonths(TodaysDate,-1)),
21,
"Calendar"},

{"Yesterday",
TodaysDate ,
TodaysDate,
20,
"Calendar"},

/////////////////////////////////////////////
// LAST 7 Days with Dates
/////////////////////////////////////////////
// Max Date
{Date.ToText((TodaysDate),"dd") & "-" & Date.ToText(TodaysDate,"MMM") & "-" & Date.ToText(TodaysDate,"yyyy"),
TodaysDate,
TodaysDate,
36,
"Last 5 Days"},

// Max Date -1
{Date.ToText((Date.AddDays(TodaysDate,+1)),"dd") & "-" & Date.ToText(Date.AddDays(TodaysDate,-1),"MMM") & "-" & Date.ToText(Date.AddDays(TodaysDate,-1),"yyyy"),
Date.AddDays(TodaysDate,-1),
Date.AddDays(TodaysDate,-1),
35,
"Last 5 Days"},

// Max Date -2
{Date.ToText((Date.AddDays(TodaysDate,-2)),"dd") & "-" & Date.ToText(Date.AddDays(TodaysDate,-2),"MMM") & "-" & Date.ToText(Date.AddDays(TodaysDate,-2),"yyyy"),
Date.AddDays(TodaysDate,-2),
Date.AddDays(TodaysDate,-2),
34,
"Last 5 Days"},

// Max Date -3
{Date.ToText((Date.AddDays(TodaysDate,-3)),"dd") & "-" & Date.ToText(Date.AddDays(TodaysDate,-3),"MMM") & "-" & Date.ToText(Date.AddDays(TodaysDate,-3),"yyyy"),
Date.AddDays(TodaysDate,-3),
Date.AddDays(TodaysDate,-3),
33,
"Last 5 Days"},

// Max Date -4
{Date.ToText((Date.AddDays(TodaysDate,-4)),"dd") & "-" & Date.ToText(Date.AddDays(TodaysDate,-4),"MMM") & "-" & Date.ToText(Date.AddDays(TodaysDate,-4),"yyyy"),
Date.AddDays(TodaysDate,-4),
Date.AddDays(TodaysDate,-4),
32,
"Last 5 Days"},

// Max Date -5
{Date.ToText((Date.AddDays(TodaysDate,-5)),"dd") & "-" & Date.ToText(Date.AddDays(TodaysDate,-5),"MMM") & "-" & Date.ToText(Date.AddDays(TodaysDate,-5),"yyyy"),
Date.AddDays(TodaysDate,-5),
Date.AddDays(TodaysDate,-5),
31,
"Last 5 Days"},

// Max Date -6
{Date.ToText((Date.AddDays(TodaysDate,-6)),"dd") & "-" & Date.ToText(Date.AddDays(TodaysDate,-6),"MMM") & "-" & Date.ToText(Date.AddDays(TodaysDate,-6),"yyyy"),
Date.AddDays(TodaysDate,-6),
Date.AddDays(TodaysDate,-6),
30,
"Last 5 Days"},

/////////////////////////////////////////////
// Current MTD
/////////////////////////////////////////////

{ "MTD (" & Date.ToText(Date.StartOfMonth(TodaysDate),"dd") & "-" & Date.ToText(TodaysDate,"dd") & " " & Date.ToText(TodaysDate,"MMM") &")" ,
Date.StartOfMonth(TodaysDate),
TodaysDate,
1,
"Current MTD"},

/////////////////////////////////////////////
// Previous MTD
/////////////////////////////////////////////

{Date.ToText(Date.StartOfMonth(Date.AddMonths(TodaysDate,-1)),"MMM") & "-" & Date.ToText(Date.StartOfMonth(Date.AddMonths(TodaysDate,-1)),"yyyy"),
Date.StartOfMonth(Date.AddMonths(TodaysDate,-1)),
Date.AddMonths(TodaysDate,-1),
1,
"Previous MTD"}

},
GetTables = List.Transform(Ranges,
each CreatePeriodTable(_{0}, _{1}, _{2}, _{3} ,_{4})),
Output = Table.Combine(GetTables),
#"Sorted Rows" = Table.Sort(Output,{{"Date", Order.Ascending}})
in
#"Sorted Rows"

pls sort me out.

[V-pazhen-msft]

@sathishramuk33 

With this overly long query, you should just post a sample pbix file instead of throwing all codes. I guess you can also explain your expected output, see if there is a another easier way to achieve it.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[sathishramuk33]

 i need this type of solution( dynamic header change based on today date) pl sort me out