Create a Simple Calculated Column in Power Pivot Data Model using DAX (Excel 2013)

We are assuming that your Power Pivot Tab is enabled.

1. Under Power Pivot Tab Click Manage in Data  Model

    2.  A new window pops up(called Power Pivot Data Model); under 'Get External Data Tab' , select SQL Server 'From Database'.

    3. Now connect to SQL Server.




    4. Click Next; then you will have two options either write your own query or get data from selected tables.



   5. Once you click Finish, Power Pivot starts loading data from  selected tables in memory. Once  it finishes, it shows a summary report and returns you to Power Pivot Window which now shows all data loaded into Power Pivot Data Model.
(Here we have selected one single  table SpecialOffer from AdventureWorks2008R2 database and will create a calculated column on no. of  days offer run)

    6. Now go to Design tab of the Ribbon and click Add.

    7. New Column is Added.

    8. Write a DAX expression in formula bar, press  {Enter}. Power Pivot computes values and adds them to a new column(named CalculatedColumn1 by default)

         DAX Formula=1.*(SpecialOffer[EndDate]-SpecialOffer[StartDate])+1

    9. To rename Column name, right click column header and choose rename column.