Power Bi: Assignment-7

tanima maiti Jun 10 2021 · 1 min read
Share this

1)Create a new Date Table using DAX

example of date table to make

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

enter the above formula to create "Date 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. 

    Date Table

    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 –

    Snapshot of Fact_sales table to be made

    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]

    Fact_Sales table is created + Total cost column added using the above formulas

    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.  

    Read next