In this exercise, you will learn how to enhance your Power BI model by adding a Date Table, creating a measure for Year-to-Date (YTD) Sales, and visualizing the results with a line chart to track sales performance throughout the year.
Tasks:
- Load the dataset into Power BI.
- Add a Date Table to the model.
- Create a relationship between both tables.
- Create a measure to calculate Year-to-Date Sales.
- Create a line chart to display the YTD sales.
Solution:
Download Dataset: PBI-2-Sales
Step-1: Load the dataset into Power BI.
Go to the Home tab > Click on Get Data > and select Text/CSV.
Step-2: After selecting the file, you can see the data preview. Click on Load.
Step-3: Add a Date Table to the model.
Go to the Modeling tab > Click on New Table.
Write the DAX code below to create a Date Table. Based on the minimum or maximum date from ‘PBI-2-Sales’, we will create the Date Table.
Calendar = CALENDAR(MIN('PBI-2-Sales'[Date]), MAX('PBI-2-Sales'[Date]))
Step-4: Create a relationship between both tables.
Go to the Model View>, and create a relationship between both tables.
Step-5: Create a line chart to display the YTD sales.
Right-click on the dataset and select “New Measure.”
YTD Sales = CALCULATE(SUM('PBI-2-Sales'[SalesAmount]), DATESYTD('Calendar'[Date]))
After that, add a line chart to the report page. Drag the Year and Month columns from the Calendar dataset to the X-axis of the chart, and drag the YTD Sales measure from the PBI-2-Sales dataset to the Y-axis.
As you can see, the YTD sales are displayed in the line chart above. I hope you enjoyed this Power BI exercise.
