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...
- Why is My Merge Not Working in PowerQuery?Excel, Power BI, PowerQueryOctober 28, 2021
- How to VLOOKUP Multiple Matching Rows Using PowerQueryPower BI, PowerQueryJanuary 12, 2021
- How to Connect PowerQuery in Power BI or Excel to a Sharepoint filePower BI, PowerQueryDecember 14, 2020
- How to use Date.IsInNextMonth in PowerQueryPower BI, PowerQueryDecember 2, 2020
- Creating a Pivot Table from Data on Multiple Sheets in an Excel WorkbookExcel, PowerQuery, UncategorizedNovember 11, 2020