Monkey Tools

Get Month Ends Function

Article Overview

A common task for financial professionals is allocating payments over multiple days or months. In fact, if you’ve read Chapter 18 of Master Your Data with Excel and Power BI, you’ll see that the creation of a function called fnGetMonthEnds is essential in some of the recipes. Of course, you can always do that manually, but why not just ask our Monkey to do it for you?

In this article, we’ll explore exactly what this Monkey does, as well as how to use it.

Launching the Get Month Ends function from the Query Monkeys menu

Building a Get Month Ends Function with our Monkey

What this Monkey does

The purpose of this button is to quickly inject the key components into your workbook so that all you need to do is use it, not build it! When invoked, this function will return a list of each month-end date that falls between the two dates you supply.

Creating the fnGetMonthEnds Function

The purpose of this button is to quickly inject the key components into your workbook so that all you need to do is use it, not build it! To create the function, simply go to:

  • Query Monkeys -> GetMonthEnds Function

The Queries & Connections pane will open (if it isn’t already open), and you’ll see your new function ready for use.

Working with the fnGetMonthEnds Function

To illustrate how the fnGetMonthEnds function works, let’s assume that we have the following table of Start and End dates in a table:

To leverage the function, we need to:

  • Select the table in the Power Query Editor
  • Go to Add Column -> Invoke Custom Function
  • Choose fnGetMonths from the Function query drop-down box
  • Change both the FromDate and ToDate data types to accept Column Names
  • Choose the columns that contain our starting and ending dates

Once you click OK, you’ll be presented with the view shown at the right; a new column of Table values.

In most cases, you’ll want to probably just want to expand those into new rows at this point, which you can do via the following steps:

  • Click the Expand icon at the top right of the fnGetMonthEnds column
  • Uncheck ‘Use original column as prefix’
  • Click OK

Once done, you will see that you have a new ‘Month End’ column with a new row for each month-end between the two dates provided.

  1. Mar 2022 to Apr 2022 dates fall into two different month-ends
  2. Apr 2022 to Jun 2022 dates fall into three different month-ends
  3. Apr 2022 only spans a single month-end

Will queries that leverage the fnGetMonthEnds Function update automatically?

Absolutely! As long as the fnGetMonthEnds function continues to exist in the workbook, any query that calls it will automatically update when they are refreshed.

Share:

Facebook
Twitter
LinkedIn

Related Posts

SCD2 Monkey

Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time – until we get

Calendar Monkey

Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time – until we get

Click Query Monkeys -> Measure Table Monkey to add measure table

Add Measure Table

Article Overview Many modelers like to store their measures on a single “Measures” table in the data model.  If you’re one of them, you may

Table Monkey

Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time – until we get

SmartFolder Monkey

Article Overview One of our favourite tricks is to create a “From Folder” solution which avoids hard-coding a file path, instead pulling it dynamically via

Copyright Excelguru Consulting Inc.