Sometimes you need to know the months in a project. So if a project runs for 4 years you would like to have 48 month periods. This blog explains how you can do this with Power Query. In this blog we use the standard Power Query options: you don’t need to mess around in the advanced Editor. Keep in mind that Power Query always returns a static table in your data model. If you have multiple projects with different starting date, you’ll better create a dynamic DAX formula in your Data model to solve that situation.
I’ve created a date table which span of four years (2017-2020). I’ve added a column with the year (number) and a column with the month (number). The definition of a date table in Power BI is a table with at least one column, that spans all dates uniquely of dates that lives in your Power BI Data model. So if you have a fact table that only contains two dates: 1 Januari 2010 and 14 April 2012, you’ll still need to have a date table with at least all dates between 1-1-2010 and 14-4-2012.
These are the steps we take to add the project month number:
- Duplicate the table and find all years unique.
- Sort the column and add an index column, starting by 0.
- Merge the copied table.
- Add the formula based on the index of the year
Start by the creating the Date table. You can create this table in all kinds of programs, e.g Excel of Power Query. I called the query SourceDate.
The first step is to copy the query. To do that you have two options: Duplicate and Reference.
The first option is an exact copy. That means all made steps will be available in the duplicated table. The second one point to the query. In this case the steps in the original query will be not be in the copy query, but the new query will just start at the end of the first Query. So the Reference looks like the faster solution, since the steps will only be executed once. But there are also drawbacks. For instance it is not possible to merge a referenced query with the query from which it is derived. So in this case we Duplicate the query.
We need to transform the new query in – again – four easy steps. First you change the date into a year, then you remove duplicates. Next you sort so the dates are always in the correct order and then you add an index column, starting at 0.
I renamed the query in YearIndex. The next step is merging the new created index number to our date table. Start with the Start ribbon option merge queries as new.
The next step is unfold the YearIndex column.
Now we can add a column with the calculation. To know the correct month number, you start with the number of the month and then add the index number multiplied by 12 (the number of month in any given year). Since the index starts with zero, the first year the month number will not be affected. Just add a Calculated Column with the formula.
Rename the query to Date. This will work like charm if the project starts at the first month of the year, but how do I correct this number if the project starts at another month(number)? In this case you need to adjust the formula with number of month passed. To show how it works I changed the starting date of the Date table to August 2017.
These are the steps we take for adjusting the start of the month:
- Duplicate the table and find the first month of the first year
- Add this value to the table
- Adjust the formula with the start month number.
To achieve this, you start with a Duplicate from the DataSource Query. Rename this column in StartMonth. Start by removing the Date column. From the column year, you need to filter the first year. Now remove column Year. From the column Month you want to know the lowest (minimum) value. Use the button Statistics, Minimum. The result is a value. Now return to the Date query.
We add the start month number before the step with the calculation, so we can adjust the formula with the new added value.
Approve you want to add a step in between and Add the number as a Custom Column.
Now you adjust the calculation by clicking the gear on the right side of the last step, named Added Custom. Don’t forget to add the 1, otherwise you are one month low.
This simple way is easy if you don’t know the M-language. If you are able to write the code yourself the queries can be made more efficient.