{"id":1944,"date":"2021-05-17T09:48:06","date_gmt":"2021-05-17T09:48:06","guid":{"rendered":"https:\/\/bi.unija.com\/en\/?p=1944"},"modified":"2021-05-17T09:48:06","modified_gmt":"2021-05-17T09:48:06","slug":"get-the-last-four-quarters-in-power-bi","status":"publish","type":"post","link":"https:\/\/bi.unija.com\/en\/get-the-last-four-quarters-in-power-bi\/","title":{"rendered":"Get the last Four Quarters in Power BI"},"content":{"rendered":"

For Power BI version 2.76.5678.782 (December 2019).<\/em><\/p>\n

When viewing business reports, we often want to see data in the last quarter, last two quarters, etc. To that end we can create a table that contains only the last few quarters and feed it to a slicer. Let\u2019s look at how to create a table of last four quarters in Power Query.<\/p>\n

Let\u2019s start with noting you need a YM sort<\/strong> column in your Calendar<\/strong> table.<\/p>\n

<\/p>\n

If you don\u2019t have YM sort column in your Calendar, simply add it with Add Column > Custom Column<\/strong>.<\/p>\n

<\/p>\n

Enter the formula [Year]*12 + [Month] <\/strong>and confirm with OK<\/strong>.<\/p>\n

We can now start our query for finding the last four quarters. We follow the steps bellow.<\/p>\n

Start by referencing Calendar<\/strong>.<\/p>\n

<\/p>\n

Remove all columns except Date<\/strong>, Year<\/strong>, Quarter<\/strong> and YM sort<\/strong> with right click, Remove Other Columns<\/strong>.<\/p>\n

<\/p>\n

Remove duplicates with right click, Remove Duplicates<\/strong>.<\/p>\n

Extract last two digits from Year<\/strong> column. Select Transform > Extract Last Characters<\/strong> and type in 2.<\/p>\n

<\/p>\n

Sort rows descending by YM sort<\/strong> column.<\/p>\n

Merge columns with selecting Quarter <\/strong>and Year<\/strong> (in that order) and right clicking Merge Columns<\/strong>.<\/p>\n

<\/p>\n

Filter Date<\/strong> column to dates that are older than the current date. Right click on Date > Date Filters > Before<\/strong>.<\/p>\n

<\/p>\n

Select is before<\/strong> and Today<\/strong> on the drop-down menu.<\/p>\n

<\/strong><\/p>\n

Dates are now filtered.<\/p>\n

<\/p>\n

Finally, filter Keep Rows > Keep Top Rows<\/strong>.<\/p>\n

<\/p>\n

Enter 4<\/strong>. The final table now looks like this.<\/p>\n

<\/p>\n

We can now load the table to the model, feed it to slicer and quickly observe our data in the last four quarters.<\/p>\n

Shortcut: use the code<\/h1>\n

Use the code bellow and paste it to Advanced Editor<\/strong> to get the same result.<\/p>\n

let<\/strong><\/p>\n

Source = Calendar,<\/strong><\/p>\n

#”Removed Other Columns” = Table.SelectColumns(Source,{“Date”,”Year”, “Quarter”, “YM sort”}),<\/strong><\/p>\n

#”Removed Duplicates” = Table.Distinct(#”Removed Other Columns”, {“Year”, “Quarter”}),<\/strong><\/p>\n

#”Extracted Last Characters” = Table.TransformColumns(#”Removed Duplicates”, {{“Year”, each Text.End(Text.From(_, “sl-SI”), 2), type text}}),<\/strong><\/p>\n

#”Sorted Rows” = Table.Sort(#”Extracted Last Characters”,{{“YM sort”, Order.Descending}}),<\/strong><\/p>\n

#”Merged Columns” = Table.CombineColumns(#”Sorted Rows”,{“Quarter”, “Year”},Combiner.CombineTextByDelimiter(“-“, QuoteStyle.None),”Quarter.1”),<\/strong><\/p>\n

#”Renamed Columns” = Table.RenameColumns(#”Merged Columns”,{{“Quarter.1”, “Quarter”}}),<\/strong><\/p>\n

#”Filtered Rows” = Table.SelectRows(#”Renamed Columns”, each [Date] < Date.From(DateTime.LocalNow())),<\/strong><\/p>\n

Custom1 = #”Filtered Rows”,<\/strong><\/p>\n

#”Kept First Rows” = Table.FirstN(Custom1,4)<\/strong><\/p>\n

in<\/strong><\/p>\n

#”Kept First Rows”<\/strong><\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

For Power BI version 2.76.5678.782 (December 2019). When viewing business reports, we often want to see data in the last quarter, last two quarters, etc. To that end we can create a table that contains only the last few quarters<\/p>\n","protected":false},"author":5,"featured_media":1956,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1944","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"acf":[],"yoast_head":"\nGet the last Four Quarters in Power BI - Bi Unija<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/bi.unija.com\/en\/get-the-last-four-quarters-in-power-bi\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Get the last Four Quarters in Power BI - Bi Unija\" \/>\n<meta property=\"og:description\" content=\"For Power BI version 2.76.5678.782 (December 2019). When viewing business reports, we often want to see data in the last quarter, last two quarters, etc. To that end we can create a table that contains only the last few quarters\" \/>\n<meta property=\"og:url\" content=\"https:\/\/bi.unija.com\/en\/get-the-last-four-quarters-in-power-bi\/\" \/>\n<meta property=\"og:site_name\" content=\"Bi Unija\" \/>\n<meta property=\"article:published_time\" content=\"2021-05-17T09:48:06+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/05\/47.png\" \/>\n\t<meta property=\"og:image:width\" content=\"720\" \/>\n\t<meta property=\"og:image:height\" content=\"480\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Branka Trifunovi\u0107\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Branka Trifunovi\u0107\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/bi.unija.com\/en\/get-the-last-four-quarters-in-power-bi\/\",\"url\":\"https:\/\/bi.unija.com\/en\/get-the-last-four-quarters-in-power-bi\/\",\"name\":\"Get the last Four Quarters in Power BI - Bi Unija\",\"isPartOf\":{\"@id\":\"https:\/\/bi.unija.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/bi.unija.com\/en\/get-the-last-four-quarters-in-power-bi\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/bi.unija.com\/en\/get-the-last-four-quarters-in-power-bi\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/05\/47.png\",\"datePublished\":\"2021-05-17T09:48:06+00:00\",\"author\":{\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10\"},\"breadcrumb\":{\"@id\":\"https:\/\/bi.unija.com\/en\/get-the-last-four-quarters-in-power-bi\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/bi.unija.com\/en\/get-the-last-four-quarters-in-power-bi\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/bi.unija.com\/en\/get-the-last-four-quarters-in-power-bi\/#primaryimage\",\"url\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/05\/47.png\",\"contentUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/05\/47.png\",\"width\":720,\"height\":480},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/bi.unija.com\/en\/get-the-last-four-quarters-in-power-bi\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/bi.unija.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Get the last Four Quarters in Power BI\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/bi.unija.com\/en\/#website\",\"url\":\"https:\/\/bi.unija.com\/en\/\",\"name\":\"Bi Unija\",\"description\":\"Just another Bi site\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/bi.unija.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10\",\"name\":\"Branka Trifunovi\u0107\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g\",\"caption\":\"Branka Trifunovi\u0107\"},\"url\":\"https:\/\/bi.unija.com\/en\/author\/branka\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Get the last Four Quarters in Power BI - Bi Unija","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:\/\/bi.unija.com\/en\/get-the-last-four-quarters-in-power-bi\/","og_locale":"en_US","og_type":"article","og_title":"Get the last Four Quarters in Power BI - Bi Unija","og_description":"For Power BI version 2.76.5678.782 (December 2019). When viewing business reports, we often want to see data in the last quarter, last two quarters, etc. To that end we can create a table that contains only the last few quarters","og_url":"https:\/\/bi.unija.com\/en\/get-the-last-four-quarters-in-power-bi\/","og_site_name":"Bi Unija","article_published_time":"2021-05-17T09:48:06+00:00","og_image":[{"width":720,"height":480,"url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/05\/47.png","type":"image\/png"}],"author":"Branka Trifunovi\u0107","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Branka Trifunovi\u0107","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/bi.unija.com\/en\/get-the-last-four-quarters-in-power-bi\/","url":"https:\/\/bi.unija.com\/en\/get-the-last-four-quarters-in-power-bi\/","name":"Get the last Four Quarters in Power BI - Bi Unija","isPartOf":{"@id":"https:\/\/bi.unija.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/bi.unija.com\/en\/get-the-last-four-quarters-in-power-bi\/#primaryimage"},"image":{"@id":"https:\/\/bi.unija.com\/en\/get-the-last-four-quarters-in-power-bi\/#primaryimage"},"thumbnailUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/05\/47.png","datePublished":"2021-05-17T09:48:06+00:00","author":{"@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10"},"breadcrumb":{"@id":"https:\/\/bi.unija.com\/en\/get-the-last-four-quarters-in-power-bi\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/bi.unija.com\/en\/get-the-last-four-quarters-in-power-bi\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bi.unija.com\/en\/get-the-last-four-quarters-in-power-bi\/#primaryimage","url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/05\/47.png","contentUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/05\/47.png","width":720,"height":480},{"@type":"BreadcrumbList","@id":"https:\/\/bi.unija.com\/en\/get-the-last-four-quarters-in-power-bi\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/bi.unija.com\/en\/"},{"@type":"ListItem","position":2,"name":"Get the last Four Quarters in Power BI"}]},{"@type":"WebSite","@id":"https:\/\/bi.unija.com\/en\/#website","url":"https:\/\/bi.unija.com\/en\/","name":"Bi Unija","description":"Just another Bi site","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/bi.unija.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10","name":"Branka Trifunovi\u0107","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g","caption":"Branka Trifunovi\u0107"},"url":"https:\/\/bi.unija.com\/en\/author\/branka\/"}]}},"_links":{"self":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1944"}],"collection":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/comments?post=1944"}],"version-history":[{"count":1,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1944\/revisions"}],"predecessor-version":[{"id":1957,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1944\/revisions\/1957"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media\/1956"}],"wp:attachment":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media?parent=1944"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/categories?post=1944"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/tags?post=1944"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}