In evaluating stock and cryptocurrency market price changes, trends from different time periods may be needed to help traders in making decision. This blog will present the method to create a visualisation of changes in monthly stock price trends based on the preceding year. Figure 1 shows a sample demonstration of the visualisation with selecting Microsoft as the Company.

Figure 1. Visualisation of monthly stock price trends based on different years

As seen in figure 1, there are 2 line charts with blue and grey colours which represent average closing price trends in the corresponding years and prior years, respectively.

Obtaining Relevant Tables

To obtain the month data in figure 1, the Date field needs to be set in the mapping. The detailed Date table can be obtained using the method listed in the following link:

https://www.thedataschool.com.au/joe-chan/how-to-create-a-date-table-in-power-bi-by-power-query/

 Mapping between Tables

Meanwhile, the mapping between date needs to be adjusted to connect the Stock and Date tables. Mapping was conducted between the two tables by Date fields, while relationship cardinality was set to ‘Many to one’. Figures 2-1 and 2-2 show the mapping configurations and relationship management, respectively.

Figure 2-1. Mapping of Stock and Date tables by ‘Date’ fields

Figure 2-2. Relationship management between Stock and Date tables

Creating Aggregated Fields

To achieve the visualisation in figure 1, average of Close Value needs to be created using DAX. Figure 3 shows the configuration of creating the average of Close Value.

Figure 3. Configuration of average Close Value

Creating Time Intelligence Component

After obtaining the average close value using DAX, time intelligence component of the chart is ready to be created. In this circumstance, the same period as the previous year was used as a base of the time intelligence component using the syntax key ‘SAMEPERIODLASTYEAR’. Figure 4 shows how to set the time intelligence component.

Figure 4. Configuration of time intelligence component based on previous year

After both average closing value and the related time intelligence fields had been made, both columns were put to Y-axis with Date (Year) component in the X-axis to obtain the visualisation in figure 1.

Creating Drilldown

While figure 1 contains the Time Intelligence component, information of stocks on a yearly basis may not be sufficient to gather insights on Closing price trends. Hence, a drilldown to the monthly basis is needed to help traders in understanding stock price seasonal patterns.

Creating the drilldown started with creating a hierarchy of date fields. For this visualisation, Hierarchy of Year and Month was created as shown in figure 5.

Figure 5. Creation of Year-Month Hierarchy

The hierarchy in figure 5 was being put to the X-axis to replace the Year field. Afterwards, the Drill Mode option in the chart was enabled to see monthly trends based on selected year. Figure 6-1 shows how Drill Mode is enabled. Meanwhile, figure 6-2 demonstrates drilldown to month level after Drill Mode has been activated and a certain year has been selected.

Figure 6-1. Activation of Drill Mode

Figure 6-2. Drilldown of visualisation from Year to Month level based on selected year 2021

Kristiadi Uisan
Author: Kristiadi Uisan