Creating a calculated table

Calculated tables are created dynamically using functions or DAX queries. They are very useful if you need to create a new table based on information in another table. For example, you could have a date table with 30 years of data. However, most of your users only look at the last 5 years of information when running most of their analysis. Instead of creating a new table you can dynamically make a new table that only stores the last 5 years of dates. In this recipe, you will use a single DAX query to filter the Master_Calendar_T table to the last 5 years of data.

How to do it...

1. Open Model.bim in the Grid view and then select the Table menu and New Calculated Table.

   

2. A new data tab is created. In the function box, enter this DAX formula to create a date calendar for the last 5 years:

           FILTER(MasterCalendar_T,  
           MasterCalendar_T[Date]>=DATEADD(MasterCalendar_T[Date],6,YEAR))

   

3. Double-click on the CalculatedTable 1 tab and rename it to Last_5_Years_T...