Generating Rows by Month for Date Ranges in Power Query

I’ve recently procured a data set that summarises company statistics at a financial year level.

I’d like to do some like-for-like analysis against these companies over time using additional time-based data, but they all operate with different financial year start and end dates.

This is somewhat challenging when the drift between them can be 11 months, purely based on when they choose to report their progress, and it’s just not straightforward to just use their declared financial year as a data point that can sensibly compare them.

A lot can happen in that time, but I can break each year out into a number of rows representing a given month, and this will give me a better level of granularity. Due to the time between annual reports, I also have some half-yearly data for those that haven’t done a full year report in a while, so I now have a 6 month window to work with in some cases, too. Fortunately a large number of my statistics can assume an even split per month.

Something like this…

My usual model for this is writing some SQL in my data warehouse, joining my date dimension on the start and end dates for the year, and grabbing out the records that correpsond to month end (effectively giving me an additional 12 rows for each row I had originally).

On to the Actual Problem

The data I have is sourced externally and not in a state I’d like to include in a database while I’m experimenting with it, so thought I’d have a go at this in Power Query.

The first hurdle I fell at was that Power Query doesn’t have a join (or Merge as it’s known in there) that will work in the same way a BETWEEN clause would work in SQL. A quick search led me to another great post by Reza, who of course, has already tackled something similar! However this works at the day-level, and Power Query has pretty good support for this — months… not so much.

Fortunately, we can still manage this with some M formulas rather than built-in transformations. At a high-level, here’s what we’re aiming to do:

1. Calculate span between start and end dates in months
2. Create a row for each full month between these two dates
3. Calculate the ‘month end’ date for each intervening row

Beyond this, I’d do things such as calculating spread of annual totals with DAX, as some more complicated modelling may be required and Power Query probably isn’t the best place to do this, particularly if there’s a dependency on other elements of the data model.

Let’s Get Solving

Once I’ve loaded my data, I’ll have something that looks like this:

Mocked-up data for 4 different companies, with 4 different finanical year start/end dates, after loading source data from Excel into Power Query

Month Span Calculation

To calculate the Month Span, click Add Column > Custom Column. In the dialog box, we’ll add our formula, e.g.:

Month span calculation, based on this post in the Power BI forums — I’ve added 1 to the total to give me a denominator

For your copy/paste convenience, here’s the raw code:

  (12 * (Date.Year([FY End]) - Date.Year([FY Start])))
+ (Date.Month([FY End]) - Date.Month([FY Start]))
+ (if Date.Day([FY End]) < Date.Day([FY Start]) 
    then -1 
    else 0
  )
+ 1

Generating Rows

Now we have the number of months, we can create the requisite number of rows. We do this by generating a list that we can subsequently expand.

Click Add Column > Custom Column and we’ll add our formula, e.g.:

M code for generating a 1..N list for the number of months in our start/end date span

Raw code:

List.Numbers(
  1,
  [Month Span]
)

At this point, we’ll now have a column containing a List entry. Click the expand button at the top of the Month List column and then Expand to New Rows, e.g.:

Expanding our list into rows

You’ll now have a Month List column with as many rows as the Month Span column indicates for that particular record. This will run from 1 to N (working like an index column within each particular company financial year).

Month End Date

I do this primarily so that everything snaps to the same place (month start might be easier, but if I’m getting additional structured metrics I want them as at the end of the month in the report I’m working on).

We can do this by adding another custom column, e.g.:

M code to use the index column to resolve month end date

Raw code:

Date.EndOfMonth(
  Date.AddMonths(
    [FY End],
    0 - [Month Span] + [Month List]
  )
)

This fulfils the objectives above, and we now have one row per month per financial year per company, e.g.:

Our complete recipe

Wrapping Up

As mentioned earlier on, there’s a need for me to do some kind of spread calculation and other things that DAX may be more useful for, depending on contextual stuff that I’m yet to explore, but for now I at least have the grain I need to work with. Now, I can get on with my analysis!

If you’ve got this far, thanks very much for reading, and hopefully this might have helped you out a little bit.