{"id":1480,"date":"2013-12-10T05:51:28","date_gmt":"2013-12-10T05:51:28","guid":{"rendered":"https:\/\/poiseddevelopers.com\/reality-tech\/?p=1480"},"modified":"2024-04-26T12:43:22","modified_gmt":"2024-04-26T12:43:22","slug":"powerpivot-rollups-of-the-previous-slicer-period-using-dax","status":"publish","type":"post","link":"https:\/\/poiseddevelopers.com\/reality-tech\/powerpivot-rollups-of-the-previous-slicer-period-using-dax\/","title":{"rendered":"PowerPivot rollups of the previous slicer period using DAX"},"content":{"rendered":"<p>SQL Data Tools is great in SQL 2012, but I had a challenge to show rollups as separate measures for the current slicer selected period, as well as a previous period.<\/p>\n<p>First I created a CurrentYear\/PreviousYear reference table for the slicer, and set up an active\/inactive pair of relationships.<\/p>\n<pre lang=\"php\">\r\nuse DBName\r\ngo\r\ncreate table [dbo].DIMYear\r\n(\r\n--id Identity ,\r\n[CurrentYear] [char] (4)\r\n,[PreviousYear] [char] (4)\r\n)\r\n<\/pre>\n<p>Then I inserted the period entries:<\/p>\n<pre lang=\"php\">\r\nINSERT INTO [DBName].[dbo].[DIMYear]\r\n           ([CurrentYear]\r\n           ,[PreviousYear])\r\n     VALUES\r\n           ('2009','2008'),\r\n           ('2010','2009'),\r\n           ('2011','2010'),\r\n           ('2012','2011'),\r\n           ('2013','2012')\r\nGO\r\n<\/pre>\n<p>I added this to the Model, and built two relationships. An \u201cActive\u201d one for the Current Year, and an inactive relationship for the PreviousYear field.<\/p>\n<p>To get the previous year, first I drop the filter using all(), then activate the dormant relationship to the previous year using the userelationship() function.  Voila!<\/p>\n<p>Here\u2019s an example, works like a charm!<\/p>\n<p>=CALCULATE(sum([CONTINUITY_CREDIT_SURPLUS_AMT_PROPORTION]),<br \/>\nall(CONTINUITY_CREDIT_DISTRIBUTION[CONTINUITY_CREDIT_WORKING_SET_YEAR]),<br \/>\nuserelationship(CONTINUITY_CREDIT_DISTRIBUTION[CONTINUITY_CREDIT_WORKING_SET_YEAR],DIMYear<\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQL Data Tools is great in SQL 2012, but I had a challenge to show rollups as separate measures for the current slicer selected period, as well as a previous period. First I created a CurrentYear\/PreviousYear reference table for the slicer, and set up an active\/inactive pair of relationships. use DBName go create table [dbo].DIMYear [&hellip;]<\/p>\n","protected":false},"author":6,"featured_media":1484,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[54],"tags":[],"class_list":["post-1480","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-business-intelligence"],"acf":[],"_links":{"self":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1480","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/comments?post=1480"}],"version-history":[{"count":3,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1480\/revisions"}],"predecessor-version":[{"id":1487,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1480\/revisions\/1487"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/media\/1484"}],"wp:attachment":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/media?parent=1480"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/categories?post=1480"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/tags?post=1480"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}