Power BI Exercise – Calculate Year-to-Date (YTD) Sales

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:

1. Load the dataset into Power BI.
2. Add a Date Table to the model.
3. Create a relationship between both tables.
4. Create a measure to calculate Year-to-Date Sales.
5. 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.