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.

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]

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)

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])

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"))

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 in Power Query

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

YM

To add YM column, we use the following formula.

= Text.Start([Month Name], 3)&" "&Text.From([Year])

YM Index

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

= [Year]*12+[Month]

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

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]

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:

Part 6, 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!