How to use Date.IsInCurrentQuarter in PowerQuery

Date.IsInCurrentQuarter is a useful PowerQuery function that allows us to check whether dates in a column of dates are in the current quarter or not. As Checking whether a date is in the current quarter isn’t something that we can do through simply clicking buttons in the User Interface in the PowerQuery editor so we need to use a little M code. However, it is a very straightforward formula  with only a single parameter. Nice and easy :-).

In the video below we look at how the function can be used to identifyif an invoice date is in the current quarter which could be useful for doing some AP or AR ageing. This in turn is then useful for cashflow forecasting.

We then look at combining the function Date.IsInCurrentQuarter with conditional wording, to change the output of True or False, which this function normally returns, to “Yes” and “No”. Yes and No can be much more useful for slicers or perhaps building more complex DAX formulas in PowerPivot or in the Data Model.

If you’d like to follow along with the video, then download the Excel file in the link to the right, and connect Power BI or PowerQuery in Excel to the file:

A little bit about the function Date.IsInCurrentQuarter

Syntax, returned values and related functions

The syntax for Date.IsInCurrentQuarter is pretty much as easy as you can get. There is only one parameter:

           =Date.IsInCurrentQuarter(Date or DateTime value or Column) as type logical

This function looks through the date value or dates in the date column in the 1st parameter and then evaluates (works out) whether the date is in the current quarter. It then returns a logical type ‘True’ or ‘False’ – so ‘True’ if the date is in the current quarter and ‘False’ if it isn’t.

When you look at Microsoft’s breakdown of functions by the way, that’s what the ‘as type logical’ or ‘as nullable type’ means; it’s the type of data that the function returns and must be formatted as such.

Please bear in mind that PowerQuery assumes that your company’s financial year runs from January to December and therefore Q1 is January to March, Q2 is April to June, Q3 is July to September and Q4 is October to December. If your company therefore doesn’t have such a financial year then you’re probably better off with a DAX function or having a separate dimension table for merging in that is specific to your company’s calendar.

 

Other Popular Finance Team Training posts on PowerQuery...

Other useful Info and links

 – If you’d like to visit the Microsoft M Code library then click here