{"id":1206,"date":"2022-06-02T13:00:21","date_gmt":"2022-06-02T11:00:21","guid":{"rendered":"https:\/\/www.flip-design.de\/?p=1206"},"modified":"2022-06-02T13:00:21","modified_gmt":"2022-06-02T11:00:21","slug":"comparison-with-dax-by-selecting-two-different-time-ranges","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=1206","title":{"rendered":"Comparison with DAX by selecting two different time ranges"},"content":{"rendered":"\n<p>A customer asks me, who he can select an alternative comparison time series inside Power BI. He wants to select the actuals and compare it with another time series. The analysis should look like this:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/06\/image-8.png\"><img decoding=\"async\" loading=\"lazy\" width=\"945\" height=\"316\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/06\/image-8.png\" alt=\"\" class=\"wp-image-1207\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/06\/image-8.png 945w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/06\/image-8-300x100.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/06\/image-8-768x257.png 768w\" sizes=\"(max-width: 945px) 100vw, 945px\" \/><\/a><\/figure>\n\n\n\n<p>He wants to see in 1 the result of the time series of the selection by 2. Inside 4 he wants to see the result of the selection done by 2. The underlying data is only one fact table.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/06\/image-9.png\"><img decoding=\"async\" loading=\"lazy\" width=\"355\" height=\"252\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/06\/image-9.png\" alt=\"\" class=\"wp-image-1208\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/06\/image-9.png 355w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/06\/image-9-300x213.png 300w\" sizes=\"(max-width: 355px) 100vw, 355px\" \/><\/a><\/figure>\n\n\n\n<p>So, I created two different timetables and connected these to the fact table:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/06\/image-10.png\"><img decoding=\"async\" loading=\"lazy\" width=\"945\" height=\"711\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/06\/image-10.png\" alt=\"\" class=\"wp-image-1209\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/06\/image-10.png 945w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/06\/image-10-300x226.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/06\/image-10-768x578.png 768w\" sizes=\"(max-width: 945px) 100vw, 945px\" \/><\/a><\/figure>\n\n\n\n<p>The relationship between the fact and the \u201ccompare date\u201d are deactivated. Now, we can create two different measures, one to sum the actual sales, and one for the comparison.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Sum of Sales = \r\nCALCULATE ( SUM ( 'Table'&#91;Sales] ), ALL ( 'Date Compare' ) )\r\n<\/code><\/pre>\n\n\n\n<p>The second one, uses the inactive relationship and also ignores the filter:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Compare Sum of Sales = \r\nCALCULATE (\r\n    SUM ( 'Table'&#91;Sales] ),\r\n    USERELATIONSHIP ( 'Date Compare'&#91;Date], 'Table'&#91;Date] ),\r\n    ALL ( 'Date Sales' )\n)\n\r\n<\/code><\/pre>\n\n\n\n<p>Now, you can select the different time series and compare the data<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/06\/image-11.png\"><img decoding=\"async\" loading=\"lazy\" width=\"768\" height=\"503\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/06\/image-11.png\" alt=\"\" class=\"wp-image-1210\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/06\/image-11.png 768w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/06\/image-11-300x196.png 300w\" sizes=\"(max-width: 768px) 100vw, 768px\" \/><\/a><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>A customer asks me, who he can select an alternative comparison time series inside Power BI. He wants to select the actuals and compare it with another time series. The analysis should look like this: He wants to see in &hellip; <a href=\"https:\/\/www.flip-design.de\/?p=1206\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1206"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1206"}],"version-history":[{"count":1,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1206\/revisions"}],"predecessor-version":[{"id":1211,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1206\/revisions\/1211"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1206"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1206"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1206"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}