Forum Discussion
Power Query Date Functions Help
Hello!
I am trying to create a query that provides different time-intelligence periods (MTD, YTD, etc.). I have been able to get a few but am unable to figure out the remaining ones. Can you please help? Here is what I have so far, and those missing are further down. Thank you!!
let
TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
Ranges = {
{"Today",
TodaysDate,
TodaysDate,
1},
{"Current Week to Date",
Date.From(Date.StartOfWeek(TodaysDate)),
TodaysDate,
2},
{"Current Month to Date",
Date.From(Date.StartOfMonth(TodaysDate)),
TodaysDate,
3},
{"Current Year to Date",
Date.From(Date.StartOfYear(TodaysDate)),
TodaysDate,
5},
{"Current Quarter to Date",
Date.From(Date.StartOfQuarter(TodaysDate)),
TodaysDate,
4}
},
GetTables = List.Transform(Ranges,
each CreatePeriodTable(_{0}, _{1}, _{2}, _{3})),
Output = Table.Combine(GetTables),
#"Sorted Rows" = Table.Sort(Output,{{"Sort", Order.Ascending}})
in
#"Sorted Rows"
Missing (NEEDED)
- Same (Complete) Month Previous Year
- Same (Complete) Quarter Previous Year
- Previous Year
- Same Month (To Date) Previous Year
- Same Quarter (To Date) Previous year
- Previous Year (To Date)
- Past 30 Days
- Previous Month
- Previous Quarter
I figured it out. New in red. Thanks!
let
TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
ThisDayLastYear = Date.From(Date.AddYears(TodaysDate,-1)),
Ranges = {
{"Today",
TodaysDate,
TodaysDate,
1},
{"Current Week to Date",
Date.From(Date.StartOfWeek(TodaysDate)),
TodaysDate,
2},
{"Current Month to Date",
Date.From(Date.StartOfMonth(TodaysDate)),
TodaysDate,
3},
{"Current Year to Date",
Date.From(Date.StartOfYear(TodaysDate)),
TodaysDate,
5},
{"Current Quarter to Date",
Date.From(Date.StartOfQuarter(TodaysDate)),
TodaysDate,
4},
{"Past 30 Days",
Date.AddDays(TodaysDate,-30) + #duration(1,0,0,0),
TodaysDate,
6},
{"Past 60 Days",
Date.AddDays(TodaysDate,-60) + #duration(1,0,0,0),
TodaysDate,
7},
{"Past 90 Days",
Date.AddDays(TodaysDate,-90) + #duration(1,0,0,0),
TodaysDate,
8},
{"Past 120 Days",
Date.AddDays(TodaysDate,-120) + #duration(1,0,0,0),
TodaysDate,
9},
{"YTD Last Year",
Date.From(Date.StartOfYear(ThisDayLastYear)),
ThisDayLastYear,
10}
},
GetTables = List.Transform(Ranges,
each CreatePeriodTable(_{0}, _{1}, _{2}, _{3})),
Output = Table.Combine(GetTables),
#"Filtered Rows" = Table.SelectRows(Output, each ([Sort] = 10)),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date", Order.Descending}})
in
#"Sorted Rows"
4 Replies
Hi Anonymous ,
I think you can solve all those fiels with measure on DAX, it's dynamic and don't need to create so many fields on your model.
Check this link: https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax
Ricardo
Hi camargos88 , thanks! I would love to, but I cannot figure out how to use these for just the Calendar table. What I am trying to do is create a Time Period slicer that has all the time periods listed below but that works for multiple pieces of data. For example, if I have a table that shows a client, their revenue, cost and profit, I want to select MTD, YTD, etc from the slicer and have each revenue, cost and proft change to MTD, YTD etc. All I have been able to figure out how to do currently is set up a DAX measure (and slicer) for Revenue only or for Profit only , but not one that controls all data. I hope I make sense!
I have been able to get the 30/60/90/120 Days but still cannot figure out the correct syntax for Previous Year, Month & Quarter and Previous Year. Month and Quarter to Date.
Any help would be greatly appreciated!
[...]
{"Past 30 Days",
Date.AddDays(TodaysDate,-30) + #duration(1,0,0,0),
TodaysDate,
6},
{"Past 60 Days",
Date.AddDays(TodaysDate,-60) + #duration(1,0,0,0),
TodaysDate,
7},
{"Past 90 Days",
Date.AddDays(TodaysDate,-90) + #duration(1,0,0,0),
TodaysDate,
8},
{"Past 120 Days",
Date.AddDays(TodaysDate,-120) + #duration(1,0,0,0),
TodaysDate,
9}[...]
I figured it out. New in red. Thanks!
let
TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
ThisDayLastYear = Date.From(Date.AddYears(TodaysDate,-1)),
Ranges = {
{"Today",
TodaysDate,
TodaysDate,
1},
{"Current Week to Date",
Date.From(Date.StartOfWeek(TodaysDate)),
TodaysDate,
2},
{"Current Month to Date",
Date.From(Date.StartOfMonth(TodaysDate)),
TodaysDate,
3},
{"Current Year to Date",
Date.From(Date.StartOfYear(TodaysDate)),
TodaysDate,
5},
{"Current Quarter to Date",
Date.From(Date.StartOfQuarter(TodaysDate)),
TodaysDate,
4},
{"Past 30 Days",
Date.AddDays(TodaysDate,-30) + #duration(1,0,0,0),
TodaysDate,
6},
{"Past 60 Days",
Date.AddDays(TodaysDate,-60) + #duration(1,0,0,0),
TodaysDate,
7},
{"Past 90 Days",
Date.AddDays(TodaysDate,-90) + #duration(1,0,0,0),
TodaysDate,
8},
{"Past 120 Days",
Date.AddDays(TodaysDate,-120) + #duration(1,0,0,0),
TodaysDate,
9},
{"YTD Last Year",
Date.From(Date.StartOfYear(ThisDayLastYear)),
ThisDayLastYear,
10}
},
GetTables = List.Transform(Ranges,
each CreatePeriodTable(_{0}, _{1}, _{2}, _{3})),
Output = Table.Combine(GetTables),
#"Filtered Rows" = Table.SelectRows(Output, each ([Sort] = 10)),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date", Order.Descending}})
in
#"Sorted Rows"
