# Power Bi: Assignment-7

tanima maiti Jun 10 2021 · 1 min read

### 1)Create a new Date Table using DAX

(i) To create a new date table: Table tools --> New table -->

(ii) To add other columns: In date table, Table tools --> New column

• Year = YEAR('Date'[Date])
• Month Number = MONTH('Date'[Date])
• Month full = FORMAT('Date'[Date],"mmmm")
• Month abbr = FORMAT('Date'[Date], "mmm")
• Quarter num = QUARTER('Date'[Date])
• Quarter full = FORMAT('Date'[Date], "\quarter Q")
• Quarter Abbr = FORMAT('Date'[Date], "\Q q")
• Each of these Formulas help in achieving the desired results.

### 2) Create a new measure to find the quarterly sales and yearly sales.

Created a separate table for measures: report view--> Home--> Enter Data--> name  it (Sales measures)--> ok.

Right click 'Sales Measures'-->New measure--> Enter following formulas to get Quaterly sales and yearly sales.

• Quarterly Sales = TOTALQTD(SUM(InvoiceLines[SalePrice]), Invoices[InvoiceDate], FILTER('Date', 'Date'[Quarter num]=1))
• Yearly Sales = TOTALYTD(SUM(InvoiceLines[SalePrice]),Invoices[InvoiceDate])
• Once Measures are created, Card view visual can be used to represent the value.

### 3) Create a new table called Fact_Sales containing the data as follows –

Creating required Table: Table tools--> New table -->

Fact_sales = SELECTCOLUMNS(InvoiceLines, " SalePrice", InvoiceLines[SalePrice],

"CostPrice", RELATED(Stock[CostPrice]),

"SpareParts", RELATED(Stock[SpareParts]),

"LabourCost", RELATED(Stock[LaborCost]),

"InvoiceDate", RELATED(Invoices[InvoiceDate]))

The above formula will create a new table and will add the selected columns (Sale price, Cost price, Labor cost, Invoice date)

To create a new "total Cost" column: table tools--> New column-->

Total Cost = Fact_sales[CostPrice]+Fact_sales[SpareParts]+Fact_sales[LabourCost]

### 4) Make manual connections in the Data Model wherever required.

Manual connections can be made by dragging the column of one table to another with the same column details.