A-7 Creating Date Dimension Table and adding related columns

Solution:

Part1: Create a Date table which has calendar dates between 2012 and 2015

Create using the DAX formula. Now mark it as date table by choosing the option.

Date Dim = CALENDAR(“01–01–2012”,”31–12–2015")

Part2: Add the below columns into date dim

A) Create Year using YEAR() function ; Quarter using QUARTER()

Year = YEAR(‘Date Dim’[Date])

Quarter = QUARTER(‘Date Dim’[Date])

Quarter Full = FORMAT(‘Date Dim’[Date],”\Quarter q”) # To call Quarter1

Month = MONTH(‘Date Dim’[Date])

Monthname = FORMAT(‘Date Dim’[Date],”MMM”)

Weekno = WEEKNUM(‘Date Dim’[Date],2) # 2 means week begins on Mon

Daynum = DAY(‘Date Dim’[Date])

Dayname = FORMAT(‘Date Dim’[Date],”DDD”)

B) Create Quarterly Sales and YearlySales using DAX formulas

QuarterlySales = CALCULATE( SUM(InvoiceLines[SalePrice]), DATESQTD(Invoices[InvoiceDate]) )

YearlySales = CALCULATE( SUM(InvoiceLines[SalePrice]), DATESYTD(Invoices[InvoiceDate]) )

Part3:

To create new fact table as below:

Go to Data view, click on New table & Name it as Fact_sales table. Use dax formula:

Fact_sales=

SELECTCOLUMNS(InvoiceLines,”Sales Price”, InvoiceLines[SalePrice], “Cost Price”, RELATED(Stock[CostPrice]), “Square Parts”, RELATED(Stock[SpareParts]), “Labour Costs”, RELATED(Stock[LaborCost]), “Invoice Date”, RELATED(Invoices[InvoiceDate]))