Excel Unplugged

Extending the Date Table – Part 2

We’re continuing our quest for beautiful and dynamic dashboards. We make the greatest strides towards that by simply extending the date table with four important columns: YM Index, Running 13, Current Month, and Current Year. In Part 1, we covered the basics – creating the columns in Excel. Similarly, let’s see how it’s done in DAX and M.

Extending the Date Table in DAX

Extending the table in DAX is very similar to the way we did it in Excel. Let’s see how it’s done.

Firstly, we start with the base table.

Extending the Date Table – Part 2, Base date table
Base table

YM Index

As we said in part 1, this is the quintessential column of our date table extension. To add a YM Index column, we type in the following formula.

='Calendar'[Year]*12+'Calendar'[Month]
Extending the Date Table – Part 2, Adding YM column
Adding YM Index column

Rolling 13

To add Rolling 13 column, we use the following formula.

=IF(AND((YEAR(TODAY())*12+MONTH(TODAY()))-'Calendar'[YM Index]>=0,(YEAR(TODAY())*12+MONTH(TODAY()))-'Calendar'[YM Index]<13),1,0)
Extending the Date Table – Part 2, Adding Rolling 13 column
Adding Rolling 13 column

It should be said. The formula above only works if our data only extends to the current month. If it goes further, we should also exclude YearMonth combinations where the subtraction of the formula above returns a negative result.

Current Month

This is a column that is quintessential to Power BI Dashboards. The reason is that the filters are sticky when you pin a visual to the Power BI DashBoard. To add the Current Month column, we use the following formula.

=IF(YEAR(TODAY())*12+MONTH(TODAY())='Calendar'[YM Index],"CURRENT MONTH", 'Calendar'[MMM YYYY])
Extending the Date Table – Part 2, Adding Current month column
Adding Current Month column

Current Year

The importance of this column is also because of Power BI DashBoards. To create the Current Year column, we use the following formula.

=IF(YEAR(TODAY())='Calendar'[Year];"CURRENT YEAR", FORMAT('Calendar'[Year], "0"))
Extending the Date Table – Part 2, Adding Current year column
Adding Current Year column

If we select the filter, we can see the “CURRENT YEAR” string appear in the place of 2021, which is the current year.

Extending the Date Table – Part 2, Adding Current year column
Current Year value

Extending the Date Table in Power Query

Let’s now do the same in Power Query. Firstly, start with the same base table.

Extending the Date Table – Part 2, Power Query base date table
Base table in Power Query

YM

To add YM column, we use the following formula.

= Text.Start([Month Name], 3)&" "&Text.From([Year])
Extending the Date Table – Part 2, Power Query adding YM
Adding YM column
Extending the Date Table – Part 2, Power Query adding YM
Adding YM column

YM Index

To add YM Index column, we use the following formula.

= [Year]*12+[Month]
Extending the Date Table – Part 2, Power Query adding YM Index
Adding YM Index column

Rolling 13

To add the Rolling 13 column we’ll have to use an fi statement. If statements in M follow the structure:

if … then …  else …

With little trial and error, we get the following working formula for Rolling 13 column.

= if Date.Year(DateTime.LocalNow())*12+Date.Month(DateTime.LocalNow())-[YM Index]>=0 and Date.Year(DateTime.LocalNow())*12+Date.Month(DateTime.LocalNow())-[YM Index]<13 then 1 else 0
Extending the Date Table – Part 2, Power Query adding Rolling 13
Adding Rolling 13 column

Current month

To add the Current Month column, we use the following formula.

= if Date.Year(DateTime.LocalNow())*12+Date.Month(DateTime.LocalNow())-[YM Index]=0 then "CURRENT MONTH" else [YM]
Extending the Date Table – Part 2, Power Query adding Current Month
Adding Current month column

That’s it! In short, our extended date table is now finished. To clarify why you absolutely need to extend the date table, quickly scroll through Part 1.

Watch the tutorial

Meanwhile, you can watch the tutorial online on our new YouTube channel!

Part 5, DAX:

Extending the date table in DAX

Part 6, M:

Extending the date table in M

Please leave us a like, comment, and subscribe for more amazing Excel tricks!

Follow us on LinkedIn.

Check out our brand new R Academy!