Assuming you were already done with the import and cleaning of your dataset in Power Query. You can then proceed to the Model Tab and build the relationship between the tables.
Let us say we have two tables in the data model which are the Sales and the Order Table, but we got to notice that both Tables have their date column as shown by the Ship Date and Order date Column in the Sales and Order table. We are told to develop a report in the form of a table of the total sales Amount and Order by year and Month.
The question is do we build a Visual with multiple tables, each referencing their date column. For us to solve this problem we can create a common Date table that can be used by the Sales and Order table.
And this can be achieved using Data Analysis Expression (DAX) functions. We can either use the CALENDAR or the CALENDARAUTO () function. The CALENDAR () function returns a contiguous range of dates based on a start and end date that are entered as arguments in the function. Alternatively, the CALENDARAUTO () function returns a contiguous, complete range of dates that are automatically determined from your dataset. For this example, we will use the CALENDARAUTO () as shown below.
Now, you have a column of dates and change the data type to Date only You also want to see columns for just the year, the month number, the week of the year, and the day of the week. You can accomplish this task by selecting New Column on the ribbon and entering the following DAX equation, which will retrieve the year from your Date table, Month, WeekNum.
Year = YEAR (Dates [Date])
Month=Format (Date [Date].[Month],”mmm”)
MonthNum = MONTH (Dates [Date])
WeekNum = WEEKNUM (Dates [Date])
DayoftheWeek = FORMAT (Dates [Date].[Day], “DDDD”)
You have now created a common date table by using DAX. This process only adds your new table to the data model; you will still need to establish relationships between your date table and the Sales and Order table. To build your visual between the Sales and Orders tables, you will need to establish a relationship between this new common date table and the Sales and Orders tables. As a result, you will be able to build visuals by using the new date table. To complete this task, go to Model tab > Manage Relationships, where you can create relationships between the common date table, the Orders and Sales tables by using the Order Date column and the ship date column. The following screenshot shows an example of one such relationship.
After you have built the relationships, you can build your Total Sales and Order Quantity by Time visual with your common date table that you developed by using the DAX. To determine the total sales, you need to add all sales because the Amount column in the Sales table only looks at the revenue for each sale, not the total sales revenue.
Total Sales = SUM (Sales [Amount])
After you have finished, you can create a table by returning to the Visualizations tab and selecting the Table visual. You want to see the total orders and sales by year and month, so you only want to include the Year and Month columns from your date table, the OrderQty and the Total Sales measure
I hope this tutorial gives you a basic overview of Excel Pivot Tables and helps you in getting started with it.
Do not forget to check out our training courses: https://www.urbizedge.com.
