Filter your data on last 30/60/90 days using this trick! // Beginners Guide to Power BI in 2021

Summary

In this tutorial, Fernando demonstrates how to create custom time-based filters in Power BI for analyzing data over the past 30, 60, and 90 days. He explains the limitations of using simple calculated columns and provides a step-by-step solution using DAX formulas to generate calculated tables with distinct time periods. By creating proper relationships and using filters effectively, users can display data dynamically, just like the time range options seen in YouTube Analytics. This video is perfect for beginners looking to enhance their Power BI reports with time intelligence features.

Takeaways

• 😀 Creating time-based filters in Power BI can help you calculate data for specific periods like the last 30, 60, or 90 days.
• 😀 The inspiration for this feature comes from YouTube Analytics, where you can view your data over different time ranges like 7, 28, or 90 days.
• 😀 You can use a Calendar Table in Power BI to manage time-based calculations for your reports.
• 😀 A simple `IF` statement can categorize data into 30, 60, or 90-day buckets based on the date difference from today.
• 😀 Using a single `IF` statement to categorize dates into buckets results in issues because each date can only belong to one bucket.
• 😀 The correct solution is to create a new calculated table that includes multiple date ranges (30, 60, and 90 days) to correctly filter overlapping data.
• 😀 To create the calculated table, use DAX with `DATESBETWEEN` and `UNION` functions to combine different time periods.
• 😀 You can create a column in the calculated table to label the periods (e.g., '30 Days', '60 Days', '90 Days') for easier identification.
• 😀 It's crucial to set up a relationship between the `Calendar` table and the newly created `PeriodTable` to allow filters to propagate correctly.
• 😀 After setting up the table and relationships, adding a horizontal filter to your report allows users to select the time range they want to view, just like YouTube Analytics.

Q & A

What is the main purpose of the video?

The main purpose of the video is to teach viewers how to create a filter in Power BI that calculates data based on the last 30, 60, and 90 days from today.

Who is the target audience for this video?

The target audience is beginners who are learning how to use Power BI.

What inspired the creation of this video?

The inspiration came from YouTube's analytics section, where users can filter data by different date ranges, and the creator wanted to replicate a similar feature in Power BI.

How does the calendar table function in the Power BI report?

The calendar table is used for time intelligence and provides a list of dates based on the order dates, helping to break down the data into different time ranges.

What was the initial approach to create the time filter?

The initial approach was to create a new column in the calendar table with a simple IF statement that would categorize the dates into 30, 60, or 90-day buckets based on the number of days from today.

Why didn’t the initial approach work as expected?

The initial approach didn’t work because it placed each date into only one bucket, which didn't allow for dates to appear in multiple buckets (e.g., a date could belong to both the last 30 and 60 days).

What solution was proposed to address the issue with the initial approach?

The solution was to create a new calculated table that groups dates into multiple buckets (30, 60, 90 days), allowing the same date to appear in more than one bucket.

How was the new table for date ranges created?

The new table was created using a calculated table formula with the DAX function 'DATESBETWEEN' to get the dates from the calendar table for specific ranges, such as the last 30, 60, or 90 days.

How is the relationship between the new periods table and the calendar table important?

The relationship between the periods table and the calendar table is crucial for ensuring that the filter propagates correctly and influences the total sales calculation, with cross-filtering set to 'both.'

What is the final result after implementing the solution?

The final result is a filter that allows users to view data for the last 30, 60, or 90 days, with each date potentially appearing in multiple buckets depending on the chosen range.