{"id":6287,"date":"2023-04-01T01:03:14","date_gmt":"2023-04-01T01:03:14","guid":{"rendered":"https:\/\/monocroft.com\/?p=6287"},"modified":"2023-04-02T05:06:27","modified_gmt":"2023-04-02T05:06:27","slug":"understanding-dax-how-to-filter-by-quarter-and-sum-with-measure","status":"publish","type":"post","link":"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/","title":{"rendered":"Understanding DAX: How to Filter by Quarter and Sum with Measure"},"content":{"rendered":"\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

This article will explore the DAX function to filter data by quarter and sum values using a measure in Power BI Desktop.<\/p>\n\n\n\n

Let’s get started.<\/p>\n\n\n\n

What is DAX?<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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. <\/p>\n\n\n\n

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.<\/p>\n\n\n\n

How to Filter by Quarter and Sum with Measure<\/h2>\n\n\n\n

You can use the DAX function to filter by quarter and sum in Power BI. Follow the steps below to do that:<\/p>\n\n\n\n

Create a table with relevant data<\/h3>\n\n\n\n

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.<\/p>\n\n\n\n

Also, the table must contain a date column that shows when the transaction or event occurred.<\/p>\n\n\n\n

Create a measure for the data you want to sum<\/h3>\n\n\n\n

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. <\/p>\n\n\n\n

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:<\/p>\n\n\n\n

Total_Revenue = SUM(Table[Revenue<\/code><\/strong>])<\/code><\/strong><\/pre>\n\n\n\n
Use the DATEADD function to filter by quarter<\/h3>\n\n\n\n
The next step is to use the DATEADD <\/code><\/strong>function to filter the data by quarter. Basically, the DATEADD <\/strong><\/code>function allows you to add a specified number of intervals to a date.<\/p>\n\n\n\n
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:<\/p>\n\n\n\n
DATEADD(Table[Date], 0, QUARTER)<\/code><\/strong><\/pre>\n\n\n\n
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. <\/p>\n\n\n\n
For example, to filter by the previous quarter, you would change the formula to:<\/p>\n\n\n\n
DATEADD(Table[Date], -1, QUARTER)<\/code><\/strong><\/pre>\n\n\n\n
Use the FILTER function to apply the quarter filter<\/h3>\n\n\n\n
The next step is to use the FILTER <\/strong><\/code>function to apply the quarter filter to your data. This function allows you to create a subset of a table based on a condition. <\/p>\n\n\n\n
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:<\/p>\n\n\n\n
FILTER(Table, DATEADD(Table[Date],0,QUARTER) = DATEADD(TODAY(),0,QUARTER))<\/strong><\/code><\/pre>\n\n\n\n
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 <\/strong><\/code>function in the formula.<\/p>\n\n\n\n
Use the SUMX function to sum the filtered data<\/h3>\n\n\n\n
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. <\/p>\n\n\n\n
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:<\/p>\n\n\n\n
Sales_by_Quarter = SUMX(FILTER(Table, DATEADD(Table[Date],0,QUARTER) = DATEADD(TODAY(),0,QUARTER)), Table[Sales])<\/strong><\/code><\/pre>\n\n\n\n
This formula will sum the sales data for each row in the filtered table. The result is the total sales for the current quarter.<\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
By following the outlined steps in this article, you can filter data by quarter and sum the sales for each product using DAX.<\/p>\n\n\n\n
If you enjoyed reading this, you can also check how to calculate the average in a dynamic way in Power BI.<\/a><\/p>\n\n\n\n
Thanks for reading!<\/p>\n","protected":false},"excerpt":{"rendered":"
The DAX (Data Analysis Expressions) is one of the most effective features in Power BI and a powerful formula language … <\/p>\n
Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":6290,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,8],"tags":[],"yoast_head":"\nUnderstanding DAX: How to Filter by Quarter and Sum with Measure - Monocroft<\/title>\n<meta name=\"description\" content=\"In this article, I will walk you through how to use DAX functions to filter by quarter and sum with measure in Power BI. Read to learn more.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Understanding DAX: How to Filter by Quarter and Sum with Measure - Monocroft\" \/>\n<meta property=\"og:description\" content=\"In this article, I will walk you through how to use DAX functions to filter by quarter and sum with measure in Power BI. Read to learn more.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/\" \/>\n<meta property=\"og:site_name\" content=\"Monocroft\" \/>\n<meta property=\"article:published_time\" content=\"2023-04-01T01:03:14+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-04-02T05:06:27+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/monocroft.com\/wp-content\/uploads\/2023\/03\/dax-function-to-filter-by-quarter-and-sum-monocroft.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"720\" \/>\n\t<meta property=\"og:image:height\" content=\"405\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Matt\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Matt\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/\"},\"author\":{\"name\":\"Matt\",\"@id\":\"https:\/\/monocroft.com\/#\/schema\/person\/3aeab97c9b5661d211c0742c48f024b3\"},\"headline\":\"Understanding DAX: How to Filter by Quarter and Sum with Measure\",\"datePublished\":\"2023-04-01T01:03:14+00:00\",\"dateModified\":\"2023-04-02T05:06:27+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/\"},\"wordCount\":804,\"publisher\":{\"@id\":\"https:\/\/monocroft.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/monocroft.com\/wp-content\/uploads\/2023\/03\/dax-function-to-filter-by-quarter-and-sum-monocroft.jpg\",\"articleSection\":[\"Guides\",\"PowerBI\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/\",\"url\":\"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/\",\"name\":\"Understanding DAX: How to Filter by Quarter and Sum with Measure - Monocroft\",\"isPartOf\":{\"@id\":\"https:\/\/monocroft.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/monocroft.com\/wp-content\/uploads\/2023\/03\/dax-function-to-filter-by-quarter-and-sum-monocroft.jpg\",\"datePublished\":\"2023-04-01T01:03:14+00:00\",\"dateModified\":\"2023-04-02T05:06:27+00:00\",\"description\":\"In this article, I will walk you through how to use DAX functions to filter by quarter and sum with measure in Power BI. Read to learn more.\",\"breadcrumb\":{\"@id\":\"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/#primaryimage\",\"url\":\"https:\/\/monocroft.com\/wp-content\/uploads\/2023\/03\/dax-function-to-filter-by-quarter-and-sum-monocroft.jpg\",\"contentUrl\":\"https:\/\/monocroft.com\/wp-content\/uploads\/2023\/03\/dax-function-to-filter-by-quarter-and-sum-monocroft.jpg\",\"width\":720,\"height\":405,\"caption\":\"Dax function to filter by quarter and sum - monocroft\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/monocroft.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Understanding DAX: How to Filter by Quarter and Sum with Measure\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/monocroft.com\/#website\",\"url\":\"https:\/\/monocroft.com\/\",\"name\":\"Monocroft\",\"description\":\"Monocroft Tech\",\"publisher\":{\"@id\":\"https:\/\/monocroft.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/monocroft.com\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/monocroft.com\/#organization\",\"name\":\"Triburge\",\"url\":\"https:\/\/monocroft.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/monocroft.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/monocroft.com\/wp-content\/uploads\/2023\/07\/monocroft-logo.png\",\"contentUrl\":\"https:\/\/monocroft.com\/wp-content\/uploads\/2023\/07\/monocroft-logo.png\",\"width\":512,\"height\":512,\"caption\":\"Triburge\"},\"image\":{\"@id\":\"https:\/\/monocroft.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/monocroft.com\/#\/schema\/person\/3aeab97c9b5661d211c0742c48f024b3\",\"name\":\"Matt\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/monocroft.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c06bcee4d38a649222493c5e9f11f7a5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c06bcee4d38a649222493c5e9f11f7a5?s=96&d=mm&r=g\",\"caption\":\"Matt\"},\"url\":\"https:\/\/monocroft.com\/author\/matthew\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Understanding DAX: How to Filter by Quarter and Sum with Measure - Monocroft","description":"In this article, I will walk you through how to use DAX functions to filter by quarter and sum with measure in Power BI. Read to learn more.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/","og_locale":"en_US","og_type":"article","og_title":"Understanding DAX: How to Filter by Quarter and Sum with Measure - Monocroft","og_description":"In this article, I will walk you through how to use DAX functions to filter by quarter and sum with measure in Power BI. Read to learn more.","og_url":"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/","og_site_name":"Monocroft","article_published_time":"2023-04-01T01:03:14+00:00","article_modified_time":"2023-04-02T05:06:27+00:00","og_image":[{"width":720,"height":405,"url":"https:\/\/monocroft.com\/wp-content\/uploads\/2023\/03\/dax-function-to-filter-by-quarter-and-sum-monocroft.jpg","type":"image\/jpeg"}],"author":"Matt","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Matt","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/#article","isPartOf":{"@id":"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/"},"author":{"name":"Matt","@id":"https:\/\/monocroft.com\/#\/schema\/person\/3aeab97c9b5661d211c0742c48f024b3"},"headline":"Understanding DAX: How to Filter by Quarter and Sum with Measure","datePublished":"2023-04-01T01:03:14+00:00","dateModified":"2023-04-02T05:06:27+00:00","mainEntityOfPage":{"@id":"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/"},"wordCount":804,"publisher":{"@id":"https:\/\/monocroft.com\/#organization"},"image":{"@id":"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/#primaryimage"},"thumbnailUrl":"https:\/\/monocroft.com\/wp-content\/uploads\/2023\/03\/dax-function-to-filter-by-quarter-and-sum-monocroft.jpg","articleSection":["Guides","PowerBI"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/","url":"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/","name":"Understanding DAX: How to Filter by Quarter and Sum with Measure - Monocroft","isPartOf":{"@id":"https:\/\/monocroft.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/#primaryimage"},"image":{"@id":"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/#primaryimage"},"thumbnailUrl":"https:\/\/monocroft.com\/wp-content\/uploads\/2023\/03\/dax-function-to-filter-by-quarter-and-sum-monocroft.jpg","datePublished":"2023-04-01T01:03:14+00:00","dateModified":"2023-04-02T05:06:27+00:00","description":"In this article, I will walk you through how to use DAX functions to filter by quarter and sum with measure in Power BI. Read to learn more.","breadcrumb":{"@id":"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/#primaryimage","url":"https:\/\/monocroft.com\/wp-content\/uploads\/2023\/03\/dax-function-to-filter-by-quarter-and-sum-monocroft.jpg","contentUrl":"https:\/\/monocroft.com\/wp-content\/uploads\/2023\/03\/dax-function-to-filter-by-quarter-and-sum-monocroft.jpg","width":720,"height":405,"caption":"Dax function to filter by quarter and sum - monocroft"},{"@type":"BreadcrumbList","@id":"https:\/\/monocroft.com\/understanding-dax-how-to-filter-by-quarter-and-sum-with-measure\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/monocroft.com\/"},{"@type":"ListItem","position":2,"name":"Understanding DAX: How to Filter by Quarter and Sum with Measure"}]},{"@type":"WebSite","@id":"https:\/\/monocroft.com\/#website","url":"https:\/\/monocroft.com\/","name":"Monocroft","description":"Monocroft Tech","publisher":{"@id":"https:\/\/monocroft.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/monocroft.com\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/monocroft.com\/#organization","name":"Triburge","url":"https:\/\/monocroft.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/monocroft.com\/#\/schema\/logo\/image\/","url":"https:\/\/monocroft.com\/wp-content\/uploads\/2023\/07\/monocroft-logo.png","contentUrl":"https:\/\/monocroft.com\/wp-content\/uploads\/2023\/07\/monocroft-logo.png","width":512,"height":512,"caption":"Triburge"},"image":{"@id":"https:\/\/monocroft.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/monocroft.com\/#\/schema\/person\/3aeab97c9b5661d211c0742c48f024b3","name":"Matt","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/monocroft.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c06bcee4d38a649222493c5e9f11f7a5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c06bcee4d38a649222493c5e9f11f7a5?s=96&d=mm&r=g","caption":"Matt"},"url":"https:\/\/monocroft.com\/author\/matthew\/"}]}},"_links":{"self":[{"href":"https:\/\/monocroft.com\/wp-json\/wp\/v2\/posts\/6287"}],"collection":[{"href":"https:\/\/monocroft.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/monocroft.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/monocroft.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/monocroft.com\/wp-json\/wp\/v2\/comments?post=6287"}],"version-history":[{"count":43,"href":"https:\/\/monocroft.com\/wp-json\/wp\/v2\/posts\/6287\/revisions"}],"predecessor-version":[{"id":6689,"href":"https:\/\/monocroft.com\/wp-json\/wp\/v2\/posts\/6287\/revisions\/6689"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/monocroft.com\/wp-json\/wp\/v2\/media\/6290"}],"wp:attachment":[{"href":"https:\/\/monocroft.com\/wp-json\/wp\/v2\/media?parent=6287"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/monocroft.com\/wp-json\/wp\/v2\/categories?post=6287"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/monocroft.com\/wp-json\/wp\/v2\/tags?post=6287"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}