The DAX (Data Analysis Expressions) is one of the most effective features in Power BI and a powerful formula language that performs a bunch of functions. One of these functions is to filter data by quarter and sum values with a measure.
Generally, data analysis and manipulation are important skills for every business to make informed decisions, and whether you are a beginner or an experienced data analyst, mastering DAX can help you unlock valuable insights from your data.
This article will explore the DAX function to filter data by quarter and sum values using a measure in Power BI Desktop.
Let’s get started.
What is DAX?
DAX (Data Analysis Expression) is a formula language designed to work with relational data and perform complex calculations and aggregations on data sets. DAX includes different functions and operators that allow users to calculate, filter data, etc.
Similar to Excel formulas, DAX has a more powerful syntax and is made to handle larger and more complex data sets, such as referencing data tables and columns, performing calculations across related tables, etc.
Furthermore, DAX supports different data types, such as numbers, text, dates, and Boolean values with one of the key features being its ability to create measures.
Finally, DAX also includes several time intelligence functions that allow users to analyze data over time. For example, calculating year-to-date totals, rolling averages over a specific time period, and so on.
How to Filter by Quarter and Sum with Measure
You can use the DAX function to filter by quarter and sum in Power BI. Follow the steps below to do that:
Create a table with relevant data
The first step is to create a table in Power BI that contains the data you want to analyze. This table should include columns with data that you want to aggregate, It could be sales, revenue, etc.
Also, the table must contain a date column that shows when the transaction or event occurred.
Create a measure for the data you want to sum
The next step is to create a measure that will aggregate the data you want to sum. To create a measure in Power BI, select the table that contains the data you want to aggregate, click on the “New Measure” button, and enter a name for your measure.
In the formula bar, enter the formula to aggregate your data. For example, if you want to sum the revenue data, your formula would be:
Total_Revenue = SUM(Table[Revenue])Use the DATEADD function to filter by quarter
The next step is to use the DATEADD function to filter the data by quarter. Basically, the DATEADD function allows you to add a specified number of intervals to a date.
In this article, I want to filter by quarter, so I will add 0 quarters to the date column. To do this, you can use the following formula:
DATEADD(Table[Date], 0, QUARTER)This formula will add 0 quarters to the date column in your table. If you want to filter by a different quarter, you can change the second argument in the formula.
For example, to filter by the previous quarter, you would change the formula to:
DATEADD(Table[Date], -1, QUARTER)Use the FILTER function to apply the quarter filter
The next step is to use the FILTER function to apply the quarter filter to your data. This function allows you to create a subset of a table based on a condition.
In this example, I want to create a subset of the table that includes only the data for the current quarter. To do this, I will use the following formula:
FILTER(Table, DATEADD(Table[Date],0,QUARTER) = DATEADD(TODAY(),0,QUARTER))This formula will create a subset of the table that includes only the data for the current quarter. However, if you want to filter by a different quarter, you can change the second argument in the DATEADD function in the formula.
Use the SUMX function to sum the filtered data
Finally, use the SUMX function to sum the data in the filtered table. The SUMX function allows you to sum the values in a column or measure for each row in a table.
In this example, I want to sum the sales data for each row in the filtered table. To do this, I will use the following formula:
Sales_by_Quarter = SUMX(FILTER(Table, DATEADD(Table[Date],0,QUARTER) = DATEADD(TODAY(),0,QUARTER)), Table[Sales])This formula will sum the sales data for each row in the filtered table. The result is the total sales for the current quarter.
Conclusion
Now, that is how to filter data by quarter and sum with a measure Power BI to create reports and visuals to make insightful business decisions.
By following the outlined steps in this article, you can filter data by quarter and sum the sales for each product using DAX.
If you enjoyed reading this, you can also check how to calculate the average in a dynamic way in Power BI.
Thanks for reading!