Last several months I have worked with OData protocol of SAP Business Bydesign using Power Query. Typical scenario: I pull data of different reports applying filters, then shape and load them to Excel 2013 data model and then do necessary calculations using DAX.
Very common task in such work is to define boundaries of report, what most often related to time frames. Some models require list of dates of last month, some – from start of last year to today, some year to date and so on.
Therefore, when query data using OData, I must generate strings like
“$filter=SOMEDATEFIELD ge datetime’yyyy-MM-ddT00:00:00′ and SOMEDATEFIELD le datetime’yyyy-MM-ddT00:00:00′
where value of ‘yyyy-MM-ddT00:00:00’ should be somehow calculated.
In SAP ByDesign standard reporting functional we have option to use relative selection in date-type fields. E.g. Invoice Date, Calendar Day, Year / Month, Year / Fiscal Period etc. In such fields we can use values like “Today”, “Tomorrow”, “Yesterday”, “Last day of Last Month”, “365 days ago” and so on.
I thought that it would be nice to have something similar in Power Query. Special library of Date.* functions that will extend current list of functions.
Define Today as
Date.Today = Date.From(DateTime.FixedLocalNow())
I expect that function DateTime.FixedLocalNow makes life of data model easier than DateTime.LocalNow. However, I did not any tests of productivity. Another note – I always change data type to Date using Date.From.
Our friends in this task are such functions as
| Date.AddDays() |
Date.AddWeeks() |
Date.AddMonths() |
Date.AddQuaters() |
Date.AddYears() |
| Duration.Days() |
Date.StartOfWeek() |
Date.StartOfMonth() |
Date.StartOfQuarter() |
Date.StartOfYear() |
|
Date.EndOfWeek() |
Date.EndOfMonth() |
Date.EndOfQuarter |
Date.EndOfYear() |
Let’s start from simple
Date.Yesterday = Date.AddDays(Date.From(Date.Today), -1)
Date.Tomorrow = Date.AddDays(Date.From(Date.Today), 1)
Here I already use Date.Today instead of repeat Date.From(DateTime.FixedLocalNow()). This makes formula more convenient to read.
More common case – “N Days Ago” and “N Days in Future”
In Power Query (M) code we should define N.
N = 3
Date.NDaysAgo = Date.From(Date.AddDays(Date.Today, -N))
Date.NDaysInFuture = Date.From(Date.AddDays(Date.Today, N))
Let’s try something with months
Date.EndOfLastMonth = Date.From(Date.EndOfMonth(Date.AddMonths(Date.Today, -1)))
Date.StartOfMonth12MAgoExcCurMonth = Date.From(Date.StartOfMonth(Date.AddMonths(Date.Today, -12)))
Date.StartOfMonth12MAgoIncCurMonth = Date.From(Date.StartOfMonth(Date.AddMonths(Date.Today, -11)))
where Exc – excluding, Inc -including.
Again, we can easily make more common case – “End of month N months ago”
N = 3
Date.EndOfMonthNMonthsAgo = Date.From(Date.EndOfMonth(Date.AddMonths(Date.Today, -N)))
Something with years
Date.StartOfPreviousYear = Date.From(Date.AddYears(Date.StartOfYear(Date.Today), -1))
// or better to call it Date.StartOfLastYear
Date.StartOfCurrentYear = Date.From(Date.StartOfYear(Date.Today))
Idea is very simple – usage of combination of standard function. However, who wants to do repetitive work? Better to have library of checked and working functions.
More functions can be found in my repository on Github
https://github.com/IvanBond/pquery
Seek for Date.* and List.Dates.* functions.
Feel free to contribute your functions.
