AdventureWorksCycle-SQL

AdventureWorks Database is a Microsoft product sample for an online transaction processing (OLTP) database. The AdventureWorks Database supports a fictitious, multinational manufacturing company called Adventure Works Cycles.

Click to view online Power BI Dashboard

Screenshots

Dashboard

Relation Model

Highlights

How to calculate the age of customers

• Using IF, ROUND, TODAY, DAY functions to calculate the exact age of customers today
• A safe fail check for the BirthDate value, divided by the average days of a year, then round the value to the closest integer.

Age (Today) = 
ROUND(
   IF('Sales vPersonDemographics'[BirthDate] < TODAY(), 
      DATEDIFF('Sales vPersonDemographics'[BirthDate], TODAY(), DAY) / 365.25, 
      0), 
0)

How to calculate the fixed value of bike sales in North America

• Using CALCULATE, and FILTER, ALL functions to calculate bikes category sales out of all categories regardless of the global filter

% Of TotalCategory = 
CALCULATE(
    SUM(vProductSalesSummary[OrderQty])/CALCULATE(
        SUM(vProductSalesSummary[OrderQty]),
        ALL(vProductSalesSummary)
    ),
    FILTER(ALL(vProductSalesSummary), vProductSalesSummary[CatName] = "bikes")
)

How to calculate the number of sales in North America

• Using CALCULATE and ALL functions to calculate the percentage of total by ignoring the filters.

% Of TotalOrderAddress = 
CALCULATE(
   COUNT('Sales SalesOrderHeader'[BillToAddressID])
)/CALCULATE(
   COUNT('Sales SalesOrderHeader'[BillToAddressID]),
   ALL('Sales SalesOrderHeader')
)

Calendar Dimension Table

During the plotting of time series data, I found it helpful to create a calender dimension table that has each date as one record for all dates in the data.

The default behaviours of Power BI is combining all values for the same day (1st, 2nd, etc.) or the same month while changing the date hierarchy to DAY or MONTH. This calendar dim table can solve this issue.

This table has week number and the start date of each month so that a continuous date period can be used for x-axis.

There are many ways to create a calendar dim table. I have used two of them: Power Query and DAX

• Create a calendar dimension table

1. Power Query:

let
    StartDate = #date(StartYear,5,1),
    EndDate = #date(EndYear,6,30),
    NumberOfDays = Duration.Days( EndDate - StartDate ),
    Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "FullDateAlternateKey"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"FullDateAlternateKey", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([FullDateAlternateKey]), type number),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([FullDateAlternateKey]), type number),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([FullDateAlternateKey]), type text),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([FullDateAlternateKey]), type number),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([FullDateAlternateKey]), type number),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([FullDateAlternateKey]), type number),
    #"Inserted Day" = Table.AddColumn(#"Inserted Week of Month", "Day", each Date.Day([FullDateAlternateKey]), type number),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([FullDateAlternateKey]) + 1, type number),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([FullDateAlternateKey]), type number),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "Day Name", each Date.DayOfWeekName([FullDateAlternateKey]), type text),
    #"Inserted Start of Week" = Table.AddColumn(#"Inserted Day Name", "Start of Week", each Date.StartOfWeek([FullDateAlternateKey]), type date),
    #"Inserted Start of Month" = Table.AddColumn(#"Inserted Start of Week", "Start of Month", each Date.StartOfMonth([FullDateAlternateKey]), type date),
    #"Inserted Short Day" = Table.AddColumn(#"Inserted Start of Month", "Short Day", each Date.ToText([FullDateAlternateKey], "ddd")),
    #"Inserted Short Month" = Table.AddColumn(#"Inserted Short Day", "Short Month", each Date.ToText([FullDateAlternateKey], "MMM"))
in
    #"Inserted Short Month"

2. DAX:

DimDate = ADDCOLUMNS(CALENDAR(MIN('Sales SalesOrderHeader'[OrderDate]), MAX('Sales SalesOrderHeader'[OrderDate]))
                        , "Short Month", FORMAT([Date], "MMM")
                        , "Short Day", FORMAT([Date], "ddd")
                        , "Year", YEAR([Date])
                        , "Month", MONTH([Date])
                        , "Quarter", QUARTER([Date])
                        , "Week of Year", WEEKNUM([Date])
                        , "Day", DAY([Date])
                        , "Day of Week", WEEKDAY([Date])
                        , "Day Name", FORMAT([Date], "dddd")
                    )