{"id":137,"date":"2023-11-10T15:23:13","date_gmt":"2023-11-10T14:23:13","guid":{"rendered":"https:\/\/bitwise.exposed\/?p=137"},"modified":"2023-11-16T18:09:05","modified_gmt":"2023-11-16T17:09:05","slug":"current-period-avoid-reporting-issues","status":"publish","type":"post","link":"https:\/\/bitwise.exposed\/index.php\/2023\/11\/10\/current-period-avoid-reporting-issues\/","title":{"rendered":"Current Period: avoid reporting issues"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Abstract, from a SQLBI article. Link to the original article on SQLBI.com click here <a href=\"https:\/\/sql.bi\/817168\">https:\/\/sql.bi\/817168<\/a>. I just could recommend to read every single article from SQLBI, Alberto Ferrari XOR Marco Russo in full length. Below some key pattern and notes from my side as my personal abstract.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Computing MTD, QTD, YTD in Power BI for the current period <\/h4>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe loading=\"lazy\" title=\"Computing MTD, QTD, YTD in Power BI for the current period\" width=\"500\" height=\"281\" src=\"https:\/\/www.youtube.com\/embed\/1xZZA9H3GB0?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">This pattern is to ensure, that MTD, QTD and YTD calculations always reflect values up to the latest transaction, automatically considering the latest available transaction in the data.<\/p>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h4 class=\"wp-block-heading\">Option 1: Calculated columns in Date table<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">One calculated column will identify dates with transactions, the second calculated column first identifies the MAX(Date[Date]) from all DatesWithTransactions and then returns a string if LastDateWithData is equal to Date[Date].<\/p>\n\n\n\n<h6 class=\"wp-block-heading\">Column 1<\/h6>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#000000;--cbp-line-number-width:calc(2 * 0.6 * 1rem);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#f2f2f2;color:#0d0d0d\">DAX<\/span><span role=\"button\" tabindex=\"0\" data-code=\"DateWithTransactions =\nVAR __LastTransactionDate =\n    MAXX (\n        {\n            MAX ( 'Sales'[Order Date] ),\n            MAX ( 'Sales'[Delivery Date] )\n        },\n        [Value]\n    )\nRETURN\n    'Date'[Date] &lt;= __LastTransactionDate\n    -- all dates eqlt LastTransDate = TRUE\" style=\"color:#000000;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki light-plus\" style=\"background-color: #FFFFFF\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #0070C1\">DateWithTransactions<\/span><span style=\"color: #000000\"> =<\/span><\/span>\n<span class=\"line\"><span style=\"color: #AF00DB\">VAR<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0070C1\">__LastTransactionDate<\/span><span style=\"color: #000000\"> =<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    <\/span><span style=\"color: #0000FF\">MAXX<\/span><span style=\"color: #000000\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #0000FF\">{<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">            <\/span><span style=\"color: #0000FF\">MAX<\/span><span style=\"color: #000000\"> ( <\/span><span style=\"color: #267F99\">&#39;Sales&#39;<\/span><span style=\"color: #795E26\">[Order Date]<\/span><span style=\"color: #000000\"> ),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">            <\/span><span style=\"color: #0000FF\">MAX<\/span><span style=\"color: #000000\"> ( <\/span><span style=\"color: #267F99\">&#39;Sales&#39;<\/span><span style=\"color: #795E26\">[Delivery Date]<\/span><span style=\"color: #000000\"> )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #0000FF\">}<\/span><span style=\"color: #000000\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #795E26\">[Value]<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #AF00DB\">RETURN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    <\/span><span style=\"color: #267F99\">&#39;Date&#39;<\/span><span style=\"color: #795E26\">[Date]<\/span><span style=\"color: #000000\"> &lt;= <\/span><span style=\"color: #0070C1\">__LastTransactionDate<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    <\/span><span style=\"color: #008000\">-- all dates eqlt LastTransDate = TRUE<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<h6 class=\"wp-block-heading\">Column 2<\/h6>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#000000;--cbp-line-number-width:calc(2 * 0.6 * 1rem);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#f2f2f2;color:#0d0d0d\">DAX<\/span><span role=\"button\" tabindex=\"0\" data-code=\"CurrentDate =\nVAR LastDateWithData =\n    CALCULATE (\n        MAX ( 'Date'[Date] ),\n        'Date'[DateWithTransactions] = TRUE,\n        REMOVEFILTERS ()\n    )\n    -- get MAXdate from all dates with transactions\nRETURN\n    IF (\n        LastDateWithData == 'Date'[Date],\n        &quot;LastDateWithData&quot;\n    )\n    -- identiy single row, where LastDate = DateDate\" style=\"color:#000000;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki light-plus\" style=\"background-color: #FFFFFF\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #0070C1\">CurrentDate<\/span><span style=\"color: #000000\"> =<\/span><\/span>\n<span class=\"line\"><span style=\"color: #AF00DB\">VAR<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0070C1\">LastDateWithData<\/span><span style=\"color: #000000\"> =<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    <\/span><span style=\"color: #0000FF\">CALCULATE<\/span><span style=\"color: #000000\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #0000FF\">MAX<\/span><span style=\"color: #000000\"> ( <\/span><span style=\"color: #267F99\">&#39;Date&#39;<\/span><span style=\"color: #795E26\">[Date]<\/span><span style=\"color: #000000\"> ),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #267F99\">&#39;Date&#39;<\/span><span style=\"color: #795E26\">[DateWithTransactions]<\/span><span style=\"color: #000000\"> = <\/span><span style=\"color: #0000FF\">TRUE<\/span><span style=\"color: #000000\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #0000FF\">REMOVEFILTERS<\/span><span style=\"color: #000000\"> ()<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    <\/span><span style=\"color: #008000\">-- get MAXdate from all dates with transactions<\/span><\/span>\n<span class=\"line\"><span style=\"color: #AF00DB\">RETURN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    <\/span><span style=\"color: #0000FF\">IF<\/span><span style=\"color: #000000\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #0070C1\">LastDateWithData<\/span><span style=\"color: #000000\"> == <\/span><span style=\"color: #267F99\">&#39;Date&#39;<\/span><span style=\"color: #795E26\">[Date]<\/span><span style=\"color: #000000\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #A31515\">&quot;LastDateWithData&quot;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    <\/span><span style=\"color: #008000\">-- identiy single row, where LastDate = DateDate<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h4 class=\"wp-block-heading\">Option 2: Using calculation groups<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Create a CalculationGroup and define a CalculationItem &#8222;LastDateWithData&#8220;.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#000000;--cbp-line-number-width:calc(2 * 0.6 * 1rem);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#f2f2f2;color:#0d0d0d\">DAX<\/span><span role=\"button\" tabindex=\"0\" data-code=\"Last date with data =\nVAR LastDateWithData =\n    CALCULATE (\n        MAX (\n            MAX ( Sales[Order Date] ),\n            MAX ( Sales[Delivery Date] )\n        ),\n        REMOVEFILTERS ()\n    )\n    -- finding last date\nRETURN\n    CALCULATE (\n        SELECTEDMEASURE (),\n        'Date'[Date] = LastDateWithData\n    )\n    -- filter DateDate = LastDate\" style=\"color:#000000;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki light-plus\" style=\"background-color: #FFFFFF\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #0070C1\">Last<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0070C1\">date<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0070C1\">with<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0070C1\">data<\/span><span style=\"color: #000000\"> =<\/span><\/span>\n<span class=\"line\"><span style=\"color: #AF00DB\">VAR<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0070C1\">LastDateWithData<\/span><span style=\"color: #000000\"> =<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    <\/span><span style=\"color: #0000FF\">CALCULATE<\/span><span style=\"color: #000000\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #0000FF\">MAX<\/span><span style=\"color: #000000\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">            <\/span><span style=\"color: #0000FF\">MAX<\/span><span style=\"color: #000000\"> ( <\/span><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #795E26\">[Order Date]<\/span><span style=\"color: #000000\"> ),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">            <\/span><span style=\"color: #0000FF\">MAX<\/span><span style=\"color: #000000\"> ( <\/span><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #795E26\">[Delivery Date]<\/span><span style=\"color: #000000\"> )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        ),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #0000FF\">REMOVEFILTERS<\/span><span style=\"color: #000000\"> ()<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    <\/span><span style=\"color: #008000\">-- finding last date<\/span><\/span>\n<span class=\"line\"><span style=\"color: #AF00DB\">RETURN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    <\/span><span style=\"color: #0000FF\">CALCULATE<\/span><span style=\"color: #000000\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #0000FF\">SELECTEDMEASURE<\/span><span style=\"color: #000000\"> (),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #267F99\">&#39;Date&#39;<\/span><span style=\"color: #795E26\">[Date]<\/span><span style=\"color: #000000\"> = <\/span><span style=\"color: #0070C1\">LastDateWithData<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    <\/span><span style=\"color: #008000\">-- filter DateDate = LastDate<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Variation: Last month with data, but column &#8218;Date'[Year Month Number] in date table neccessary.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#000000;--cbp-line-number-width:calc(2 * 0.6 * 1rem);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#f2f2f2;color:#0d0d0d\">DAX<\/span><span role=\"button\" tabindex=\"0\" data-code=\"Last month with data =\nVAR LastDateWithData =\n    CALCULATE (\n        MAX (\n            MAX ( Sales[Order Date] ),\n            MAX ( Sales[Delivery Date] )\n        ),\n        REMOVEFILTERS ()\n    )\nVAR LastMonthWithData =\n    LOOKUPVALUE (\n        'Date'[Year Month Number],\n        'Date'[Date], LastDateWithData\n    )\nRETURN\n    CALCULATE (\n        SELECTEDMEASURE (),\n        'Date'[Year Month Number] = LastMonthWithData,\n        REMOVEFILTERS ( 'Date' )\n    )\" style=\"color:#000000;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki light-plus\" style=\"background-color: #FFFFFF\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #0070C1\">Last<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0070C1\">month<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0070C1\">with<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0070C1\">data<\/span><span style=\"color: #000000\"> =<\/span><\/span>\n<span class=\"line\"><span style=\"color: #AF00DB\">VAR<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0070C1\">LastDateWithData<\/span><span style=\"color: #000000\"> =<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    <\/span><span style=\"color: #0000FF\">CALCULATE<\/span><span style=\"color: #000000\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #0000FF\">MAX<\/span><span style=\"color: #000000\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">            <\/span><span style=\"color: #0000FF\">MAX<\/span><span style=\"color: #000000\"> ( <\/span><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #795E26\">[Order Date]<\/span><span style=\"color: #000000\"> ),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">            <\/span><span style=\"color: #0000FF\">MAX<\/span><span style=\"color: #000000\"> ( <\/span><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #795E26\">[Delivery Date]<\/span><span style=\"color: #000000\"> )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        ),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #0000FF\">REMOVEFILTERS<\/span><span style=\"color: #000000\"> ()<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #AF00DB\">VAR<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0070C1\">LastMonthWithData<\/span><span style=\"color: #000000\"> =<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    <\/span><span style=\"color: #0000FF\">LOOKUPVALUE<\/span><span style=\"color: #000000\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #267F99\">&#39;Date&#39;<\/span><span style=\"color: #795E26\">[Year Month Number]<\/span><span style=\"color: #000000\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #267F99\">&#39;Date&#39;<\/span><span style=\"color: #795E26\">[Date]<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #0070C1\">LastDateWithData<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #AF00DB\">RETURN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    <\/span><span style=\"color: #0000FF\">CALCULATE<\/span><span style=\"color: #000000\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #0000FF\">SELECTEDMEASURE<\/span><span style=\"color: #000000\"> (),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #267F99\">&#39;Date&#39;<\/span><span style=\"color: #795E26\">[Year Month Number]<\/span><span style=\"color: #000000\"> = <\/span><span style=\"color: #0070C1\">LastMonthWithData<\/span><span style=\"color: #000000\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #0000FF\">REMOVEFILTERS<\/span><span style=\"color: #000000\"> ( <\/span><span style=\"color: #267F99\">&#39;Date&#39;<\/span><span style=\"color: #000000\"> )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    )<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Variation: Last month with data, without column &#8218;Date'[Year Month Number] <\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#000000;--cbp-line-number-width:calc(2 * 0.6 * 1rem);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#f2f2f2;color:#0d0d0d\">DAX<\/span><span role=\"button\" tabindex=\"0\" data-code=\"Last month with data (no Date dependencies) =\nVAR LastDateWithData =\n    CALCULATE (\n        MAX (\n            MAX ( Sales[Order Date] ),\n            MAX ( Sales[Delivery Date] )\n        ),\n        REMOVEFILTERS ()\n    )\nRETURN\n    CALCULATE (\n        SELECTEDMEASURE (),\n        PARALLELPERIOD (\n            TREATAS (\n                { LastDateWithData },\n                'Date'[Date]\n            ),\n            0,\n            MONTH\n        )\n    )\" style=\"color:#000000;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki light-plus\" style=\"background-color: #FFFFFF\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #0070C1\">Last<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0070C1\">month<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0070C1\">with<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0070C1\">data<\/span><span style=\"color: #000000\"> (<\/span><span style=\"color: #0070C1\">no<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0070C1\">Date<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0070C1\">dependencies<\/span><span style=\"color: #000000\">) =<\/span><\/span>\n<span class=\"line\"><span style=\"color: #AF00DB\">VAR<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0070C1\">LastDateWithData<\/span><span style=\"color: #000000\"> =<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    <\/span><span style=\"color: #0000FF\">CALCULATE<\/span><span style=\"color: #000000\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #0000FF\">MAX<\/span><span style=\"color: #000000\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">            <\/span><span style=\"color: #0000FF\">MAX<\/span><span style=\"color: #000000\"> ( <\/span><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #795E26\">[Order Date]<\/span><span style=\"color: #000000\"> ),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">            <\/span><span style=\"color: #0000FF\">MAX<\/span><span style=\"color: #000000\"> ( <\/span><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #795E26\">[Delivery Date]<\/span><span style=\"color: #000000\"> )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        ),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #0000FF\">REMOVEFILTERS<\/span><span style=\"color: #000000\"> ()<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #AF00DB\">RETURN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    <\/span><span style=\"color: #0000FF\">CALCULATE<\/span><span style=\"color: #000000\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #0000FF\">SELECTEDMEASURE<\/span><span style=\"color: #000000\"> (),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #0000FF\">PARALLELPERIOD<\/span><span style=\"color: #000000\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">            <\/span><span style=\"color: #0000FF\">TREATAS<\/span><span style=\"color: #000000\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">                <\/span><span style=\"color: #0000FF\">{<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0070C1\">LastDateWithData<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0000FF\">}<\/span><span style=\"color: #000000\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">                <\/span><span style=\"color: #267F99\">&#39;Date&#39;<\/span><span style=\"color: #795E26\">[Date]<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">            ),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">            <\/span><span style=\"color: #098658\">0<\/span><span style=\"color: #000000\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">            <\/span><span style=\"color: #0000FF\">MONTH<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    )<\/span><\/span><\/code><\/pre><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Abstract, from a SQLBI article. Link to the original article on SQLBI.com click here https:\/\/sql.bi\/817168. I just could recommend to read every single article from SQLBI, Alberto Ferrari XOR Marco Russo in full length. Below some key pattern and notes from my side as my personal abstract. Computing MTD, QTD, YTD in Power BI for [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":138,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,16,8],"tags":[],"class_list":["post-137","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-dax","category-pattern","category-time-intelligence"],"_links":{"self":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts\/137","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/comments?post=137"}],"version-history":[{"count":18,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts\/137\/revisions"}],"predecessor-version":[{"id":233,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts\/137\/revisions\/233"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/media\/138"}],"wp:attachment":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/media?parent=137"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/categories?post=137"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/tags?post=137"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}