Relative dates and periods in Power Query

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.

Advertisements

One thought on “Relative dates and periods in Power Query

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s