Overview

In Power BI, we can achieve drill-down functionality. Power BI facilitates Year-QTY-Month drill down automatically for the columns that have the Date data type.

Now, there are some situations in which we do not have a Date Column available in our dataset. At that time, what to do to achieve the drill down?

I have added some sample data sources and Power BI files on GitHub. You can download it using the following link.

https://github.com/Dhruvinshah16/Power-BI/tree/master/PBI-Year-QTY-MonthDrillDownSample

This article is the solution for this.

Let’s get started!

We have the following types of datasets. We have columns.

Dataset

Now, let us load the data in Power BI.

Step 1. Go to Home and click on "Edit Queries".

Edit Queries

Step 2. Create a duplicate column of the Month and rename it “Month-Sort”.

Month-Sort

Please refer to the following screenshot.

New Column

Click on "Close and Apply".

Step 3. Create a calculated column using the following formula.

SortNumber = SWITCH(Table1[Month-Sort], "Jan",01, "Feb",2, "Mar",3, "Apr",4, "May",5, "Jun",6, "Jul",7, "Aug",8, "Sep",9, "Oct",10, "Nov",11, "Dec",12).

Step 4. Select the Month column.

From the ribbon, select Modelling, and from the Sort by Column, select “SortNumber”.

Modeling

Step 5. Now, let’s test the result.

Conclusion

This is how we can create a Year-QTY-Month drill-down in Power BI.

Stay connected with me for amazing articles.