Data Modelling - Business Analytics - Assignment 7

#businessanalytics #powerbi #ineuron #analysis #dax

adarsh raj Jun 03 2021 · 2 min read
Share this

Hello Friends,

We are back with new blog where we will learn about creating measures with the help of questions given in assignment 7 of Business Analytics.

We have Car Sales Dataset provided.  Below is the screen shot of the question that we need to solve

So without wasting any further time, let's start solving the questions: - 

1. Create a new Date Table using DAX

We need to write the following DAX in order to create a new Date Table. First we need to go to Data table view -- > Home --- > New Table.   Once you click New Table. You need to write formula in the Formula bar that creates that date column. The formula would be as follows: - 

Date Table = CALENDAR(date(2012,1,1),date(2015,12,31))

There is one more requirement given in the question. They want us to create the Date Table with the below columns: - 

Lets start creating these columns one by one.

Year = YEAR('Date Table'[Date])

MonthNum = MONTH('Date Table'[Date])

Month Full = FORMAT('Date Table'[Date], "MMMM")

Month Full = FORMAT('Date Table'[Date], "MMMM")

Quarter Num = QUARTER('Date Table'[Date])

Quarter Full = FORMAT('Date Table'[Date],"\Quarter q")

Quarter Abbr = FORMAT('Date Table'[Date],"\Qq")

Once you are done with your DAX, your Date table would look like this: - 

2. Create a new measure to find the quarterly sales.

In order to create measure for quarterly sales, we need to do following things: - 

Measure Tools ---> Quick Measure

Once you click Quick Measure, we can write DAX that helps us to give Quarterly sales : - 

Quarterly Sales = TOTALQTD(SUM(InvoiceLines[SalePrice]),'Date Table'[Date])

3. Create a new measure to find the yearly sales.

Same as above we can create the yearly sales measure as well. We just need to write the below code: - 

Yearly Sales = TOTALYTD(SUM(InvoiceLines[SalePrice]),'Date Table'[Date])

Once you have done this, we can create the visualization as well for quarterly and yearly sales: - 

4. Create a new table called Fact_Sales containing the data as follows –

We need to add these columns from different tables given in the dataset. Our DAX would look like this: - 

Fact_Sales = SELECTCOLUMNS(InvoiceLines,"Sales Price",InvoiceLines[SalePrice],"Cost Price",RELATED(Stock[CostPrice]), "Spare Parts",RELATED(Stock[SpareParts]), "Labor Cost",RELATED(Stock[LaborCost]), "Invoice Date",RELATED(Invoices[InvoiceDate]))

Once you execute this we will get the desired table. however, we see that we still don't have "Total Cost" column in the table. In order to do that, we simply need to do add a new column and add a query as follows: - 

Total Cost = Fact_Sales[Cost Price] - Fact_Sales[Spare Parts] - Fact_Sales[Labor Cost]

Once this is done, you can get the desired table: - 

In the next blog we will learn more on these DAX.

Till then Goodbye!!

Read next