<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Nilantha KM on Medium]]></title>
        <description><![CDATA[Stories by Nilantha KM on Medium]]></description>
        <link>https://medium.com/@nilantha.sac?source=rss-60fe5c3989fd------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*1BFv2GCltyL35bI2.jpg</url>
            <title>Stories by Nilantha KM on Medium</title>
            <link>https://medium.com/@nilantha.sac?source=rss-60fe5c3989fd------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 11:10:35 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@nilantha.sac/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[TOTALYTD() VS DATESYTD() in Power BI]]></title>
            <link>https://medium.com/@nilantha.sac/totalytd-vs-datesytd-in-power-bi-8cbdddd91531?source=rss-60fe5c3989fd------2</link>
            <guid isPermaLink="false">https://medium.com/p/8cbdddd91531</guid>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[dax]]></category>
            <dc:creator><![CDATA[Nilantha KM]]></dc:creator>
            <pubDate>Tue, 18 Nov 2025 20:16:46 GMT</pubDate>
            <atom:updated>2025-11-18T20:16:46.985Z</atom:updated>
            <content:encoded><![CDATA[<p>In this article, I’m aiming to answer these key questions.</p><ol><li>Difference between TOTALYTD() VS DATESYTD()?</li><li>Is it possible to use DATESYTD() inside TOTALYTD()?</li><li>Can we calculate the Total YTD value without using the TOTALYTD() function?</li></ol><p>To answer these questions, I’m going to use the following tables and data models.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*y58uEIDNHUq3yRoNmVkLLQ.png" /><figcaption>Figure 1</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/943/1*exXWQsUL3G49Ik5uv8IU7Q.png" /><figcaption>Figure 2</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yNQihKRtYNeJG5EqgKhfUg.png" /><figcaption>Figure 3</figcaption></figure><p>Here, I have calculated Total Sales</p><p><strong>Total sales = CALCULATE(SUMX(Sales,Sales[quantity]*RELATED(Products[product_cost])))</strong></p><p>Here, I have created another measure using the <strong>TOTALYTD() </strong>function</p><p><strong>Total YTD = TOTALYTD([Total sales],’Calendar’[date])</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/601/1*v0RhnmuZ1cymbMNfs1kEGw.png" /><figcaption>Figure 4</figcaption></figure><p>I will now create a measure using both <strong>TOTALYTD()</strong> and <strong>DATESYTD()</strong>, as shown in Figure 5.</p><p><strong>Total YTD using DatesYTD = TOTALYTD([Total sales],DATESYTD( ‘Calendar’[date]))</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/882/1*GiY5pYHKVWfgGP993aBv1Q.png" /><figcaption>Figure 5</figcaption></figure><p>But both measures return the same value.</p><p>Next, I will create another measure as follows.</p><p><strong>TotalYTD with cal function = CALCULATE([Totalsales],DATESYTD(‘Calendar’[date]))</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qrZPeZRbDZmuShH3WpOqUg.png" /><figcaption>Figure 6</figcaption></figure><p>As shown in Figure 6, all three measures return the same value. So, the conclusion is that the answer to both the second and third questions is “<strong>YES”</strong>.</p><p>Sources:<a href="https://learn.microsoft.com/en-us/dax/">https://learn.microsoft.com/en-us/dax/</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8cbdddd91531" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How RELATED and RELATEDTABLE work in Power BI]]></title>
            <link>https://medium.com/@nilantha.sac/how-related-and-relatedtable-work-in-power-bi-06b86a17e1c9?source=rss-60fe5c3989fd------2</link>
            <guid isPermaLink="false">https://medium.com/p/06b86a17e1c9</guid>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[dax]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Nilantha KM]]></dc:creator>
            <pubDate>Fri, 31 Oct 2025 19:18:21 GMT</pubDate>
            <atom:updated>2025-10-31T19:18:21.877Z</atom:updated>
            <content:encoded><![CDATA[<p>Let’s consider a scenario where we’re working with <strong>Sales, Products</strong> tables, and a data model as shown in Figures 1, 2, and 3.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*i0qgQm5_RDWpwBqc5-ilDQ.png" /><figcaption>Figure 1</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/763/1*wGew2S9o6ImrUgXa_ucLWg.png" /><figcaption>Figure 2</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*i0b0UpJTm5KnLPHuZ6SSGQ.png" /><figcaption>Figure 3</figcaption></figure><p>In this scenario, we need to bring a column from the <strong>Products</strong> table into the <strong>Sales</strong> table. For example, we want to include the product_retail_price in the Sales data to perform another calculation. To achieve the desired result, follow these simple steps:</p><p>Click on the <strong>New Column</strong> icon under the <strong>Column tools</strong> tab in the Power BI menu bar, and enter the following DAX formula.</p><p><strong>Product Retail Price = RELATED(Products[product_retail_price])</strong></p><p>Once you press <strong>Enter</strong>, Power BI creates the new column and adds it to the <strong>Sales</strong> table as shown in Figure 4.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/962/1*hV8FfF8gBq2QOVclv0CieQ.png" /><figcaption>Figure 4</figcaption></figure><p>Let’s move to the RELATEDTABLE function. Imagine we want to do the opposite of what we did before. For example, this time we need to bring the <strong>quantity </strong>column from the <strong>Sales</strong> table into the <strong>Products</strong> table and create a new column there.</p><p>To achieve the desired result, follow these simple steps:</p><p>Click on the <strong>New Column</strong> icon under the <strong>Column tools</strong> tab in the Power BI menu bar, and enter the following DAX formula.</p><p><strong>Total Qty = SUM(Sales[quantity])</strong></p><p><strong>Total qty ByProduct_id = SUMX(RELATEDTABLE(Sales),[Total Qty])</strong></p><p>Here, I’ve created the total sum across different products.</p><p>Once you press <strong>Enter</strong>, Power BI creates the new column and adds it to the <strong>Products</strong> table as shown in Figure 5.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*P-dtmAtpfD12ABdESVAXQA.png" /><figcaption>Figure 5</figcaption></figure><p>One important thing to remember is that to use these two functions, there should be a <strong>one-to-many relationship</strong> between the <strong>dimension table</strong> and the <strong>fact table</strong>.</p><p>Sources:<a href="https://learn.microsoft.com/en-us/dax/">https://learn.microsoft.com/en-us/dax/</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=06b86a17e1c9" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Exploring the SUM, CALCULATE, FILTER, and VALUES Functions in Power BI DAX]]></title>
            <link>https://medium.com/@nilantha.sac/exploring-the-sum-calculate-filter-and-values-functions-in-power-bi-dax-9f8b628dc444?source=rss-60fe5c3989fd------2</link>
            <guid isPermaLink="false">https://medium.com/p/9f8b628dc444</guid>
            <category><![CDATA[power-bi-tutorials]]></category>
            <category><![CDATA[dax]]></category>
            <category><![CDATA[data-analyst]]></category>
            <category><![CDATA[microsoft-power-bi]]></category>
            <category><![CDATA[power-bi]]></category>
            <dc:creator><![CDATA[Nilantha KM]]></dc:creator>
            <pubDate>Tue, 07 Oct 2025 18:56:53 GMT</pubDate>
            <atom:updated>2025-10-07T18:56:53.028Z</atom:updated>
            <content:encoded><![CDATA[<p>Suppose we have a data model as shown in Figure 1. And I have calculated two measures as follows.</p><h3>Total Qty = SUM(Sales[quantity])</h3><h3>Total Qty Cal = CALCULATE([Total Qty])</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QMBgO_33dx6ecpXeaSXn8Q.png" /><figcaption>Figure 1</figcaption></figure><p>Next, I added two card visuals to showcase the results of the measures created earlier, as shown in Figure 2.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/763/1*5zoTPe2wVPijYaKV7ct_8w.png" /><figcaption>Figure 2</figcaption></figure><p>Both measures return the same value — so you might wonder, what’s the real benefit of using the <strong>CALCULATE</strong> function?</p><p>The key advantage of <strong>CALCULATE</strong> is its ability to apply filters dynamically. For example, let’s say we want to calculate the total sales quantity <strong>only</strong> for stores where the store type is <em>Supermarket</em>. So we can modify the DAX code as follows. And the result will be shown in Figure 3.</p><h3>Total Qty Cal = CALCULATE([Total Qty],Stores[store_type]=”Supermarket”)</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/782/1*QCimDQdNx_IY7fK_uo_MjQ.png" /><figcaption>Figure 3</figcaption></figure><p>Now, let’s introduce another function — <strong>FILTER</strong> — into the <em>Total Qty Cal</em> measure.<br>With this, we can rewrite the <em>Total Qty Cal</em> measure as follows:</p><h3>Total Qty Cal = CALCULATE([Total Qty],FILTER(Stores,Stores[store_type]=”Supermarket”))</h3><p>We still get the same results, as shown in Figure 4. So, why should we use the <strong>FILTER</strong> function? The main reason is <strong>performance</strong>.</p><p>By using <strong>FILTER</strong>, we instruct the Power BI engine to first filter only the records in the <em>Store</em> table where the store type is <em>Supermarket</em>, and then perform the calculation. This approach can lead to a <strong>significant performance improvement</strong>, as Power BI doesn’t need to process all records — it filters the relevant ones first, then carries out the calculation efficiently.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/857/1*-jRoFDfs9fVmSb-1o3A7qg.png" /><figcaption>Figure 4</figcaption></figure><p>Now, let’s introduce another function — VALUES— into the <em>Total Qty Cal</em> measure.<br>With this, we can rewrite the <em>Total Qty Cal</em> measure as follows:</p><h3>Total Qty Cal = CALCULATE([Total Qty],FILTER(VALUES(Stores[store_type]),Stores[store_type]=”Supermarket”))</h3><p>We still get the same results, as shown in Figure 5. So, why should we use the <strong>VALUES </strong>function? The main reason is again <strong>performance</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DJeo0yXIdXWWlv9kXYqSvA.png" /><figcaption>Figure 5</figcaption></figure><p>By using the <strong>VALUES</strong> function, we instruct the Power BI engine to filter only the <strong>store_type</strong> column in the <em>Store</em> table where the store type is <em>Supermarket</em>, and then perform the calculation.</p><p>Unlike the <strong>FILTER</strong> function, which returns all columns from the table, <strong>VALUES</strong> focuses on a specific column, reducing the amount of data being processed. This targeted approach can lead to <strong>better performance</strong> and more efficient calculations.</p><p>Source: <a href="https://learn.microsoft.com/en-us/dax/">https://learn.microsoft.com/en-us/dax/</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9f8b628dc444" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Spotting Highs and Lows in Sales Trends(In a Power BI line chart)]]></title>
            <link>https://medium.com/@nilantha.sac/spotting-highs-and-lows-in-sales-trends-in-a-power-bi-line-chart-69c1a6d62d5d?source=rss-60fe5c3989fd------2</link>
            <guid isPermaLink="false">https://medium.com/p/69c1a6d62d5d</guid>
            <category><![CDATA[data-analyst]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[dax]]></category>
            <dc:creator><![CDATA[Nilantha KM]]></dc:creator>
            <pubDate>Fri, 26 Sep 2025 05:32:25 GMT</pubDate>
            <atom:updated>2025-09-26T05:34:15.846Z</atom:updated>
            <content:encoded><![CDATA[<p>Suppose we have a sales dataset. We can visualize the sales values by region, as shown in Figure 1.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/885/1*4bZ5WlYgweWrFlhn_4TvGg.png" /><figcaption>Figure 1</figcaption></figure><p>We have a requirement to display the highest and lowest values on the line.</p><p>Let’s write the DAX.</p><p><strong>Max Value =</strong></p><p><strong>var max_point=MAXX(ALL(Sales),[Total sales])</strong></p><p><strong>var check_max_point=IF(max_point=[Total sales],max_point,BLANK())</strong></p><p><strong>RETURN check_max_point</strong></p><p>Here, I used the <strong>MAXX(</strong><a href="https://learn.microsoft.com/en-us/dax/maxx-function-dax"><strong>read more</strong></a><strong>) </strong>function instead of <strong>MAX(</strong><a href="https://learn.microsoft.com/en-us/dax/max-function-dax"><strong>read more</strong></a><strong>) </strong>because the <strong>MAX </strong>function doesn’t allow us to control the filter context.In the DAX formula, max_point returns the maximum sales value, while [Total Sales] giving the sales for each individual region. When we use region as the filter context, each region’s sales are compared against max_point, which identifies the maximum data point.</p><p>In the same way, we can write a formula to calculate the minimum point as well.</p><p><strong>Min_Value =</strong></p><p><strong>var min_point=MINX(ALL(Sales),[Total sales])</strong></p><p><strong>var check_min_point=IF(min_point=[Total sales],min_point,BLANK())</strong></p><p><strong>RETURN check_min_point</strong></p><p>The final result looks like this, as shown in Figure 2.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/816/1*ZLO1DS6mBAE1k-PMcfhqzQ.png" /><figcaption>Figure 2.</figcaption></figure><p>Sources:<a href="https://learn.microsoft.com/en-us/dax/">https://learn.microsoft.com/en-us/dax/</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=69c1a6d62d5d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Cumulative Totals in Power BI: Why the ALL DAX Function Matters]]></title>
            <link>https://medium.com/@nilantha.sac/cumulative-totals-in-power-bi-why-the-all-dax-function-matters-b2f7476c6f25?source=rss-60fe5c3989fd------2</link>
            <guid isPermaLink="false">https://medium.com/p/b2f7476c6f25</guid>
            <category><![CDATA[dax]]></category>
            <category><![CDATA[power-bi]]></category>
            <dc:creator><![CDATA[Nilantha KM]]></dc:creator>
            <pubDate>Thu, 25 Sep 2025 04:37:21 GMT</pubDate>
            <atom:updated>2025-09-25T04:37:21.418Z</atom:updated>
            <content:encoded><![CDATA[<p>Suppose we have a table as shown in Figure 1, and we need to calculate the running total or cumulative total.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/465/1*2b_7Bb23-1qErQVkTf6csA.png" /><figcaption>Figure 1</figcaption></figure><p>When we place the Year and the Sum of Sales columns in a table, they appear as shown below (Figure 2). But this is not the expected result.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/210/1*V2q8l8Zn2Sz-uB41tjAtIA.png" /><figcaption>Figure 2</figcaption></figure><p>We can calculate the running total using the formula below.</p><p><strong>Running Total = CALCULATE(SUM(‘Sample table’[Sales]),FILTER(‘Sample table’,’Sample table’[Date]&lt;=MAX(‘Sample table’[Date])))</strong></p><p>But still, we get the same result as shown below (Figure 3)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*G-4-Edddasij3NRBX15gaQ.png" /><figcaption>Figure 3</figcaption></figure><p>To get the expected result, we’ll make a small adjustment to the calculation, as shown below(Figure 4).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Dd0JAcYuxZbrOOF9R6fJUA.png" /><figcaption>Figure 4</figcaption></figure><p>The only change is adding the <strong>ALL(</strong><a href="https://learn.microsoft.com/en-us/dax/all-function-dax"><strong>read</strong></a><strong>) </strong>DAX function to the formula(Figure 4). Now we are getting the correct result.</p><p>The <strong>ALL </strong>function does the magic here — it ignores all existing filters and returns all rows in the table.</p><p>Here is the official definition of the ALL function</p><p><strong><em>“Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table.”</em></strong></p><p>Resources:<a href="https://learn.microsoft.com/en-us/dax/">https://learn.microsoft.com/en-us/dax/</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b2f7476c6f25" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Exploring the RANKX DAX function in Power BI]]></title>
            <link>https://medium.com/@nilantha.sac/exploring-the-rankx-dax-function-in-power-bi-4a713a9f4a0b?source=rss-60fe5c3989fd------2</link>
            <guid isPermaLink="false">https://medium.com/p/4a713a9f4a0b</guid>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[dax]]></category>
            <dc:creator><![CDATA[Nilantha KM]]></dc:creator>
            <pubDate>Wed, 24 Sep 2025 20:51:48 GMT</pubDate>
            <atom:updated>2025-09-24T20:51:48.189Z</atom:updated>
            <content:encoded><![CDATA[<p>Suppose we have sales data as shown in Figure 1, a data model as shown in Figure 2, and we need to display the top N product brands based on user selection. Ex: TOP 5, TOP 3…</p><p>To perform this calculation, we’ll use the <strong>RANKX(</strong><a href="https://learn.microsoft.com/en-us/dax/rankx-function-dax"><strong><em>more</em></strong></a><strong>) </strong>and <strong>SELECTEDVALUE(</strong><a href="https://learn.microsoft.com/en-us/dax/selectedvalue-function-dax"><strong><em>more</em></strong></a><strong>) </strong>DAX functions.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/587/1*NP42T-vj7EFblV8_4ULJUw.png" /><figcaption>Figure 1</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/747/1*B3BoJjjWRf0bTm0nMDYKjA.png" /><figcaption>Figure 2</figcaption></figure><p>To display the top products slicer, I have created a table as shown in Figure 3.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/528/1*FZTSYWJEfyqyFchmr7MT9g.png" /><figcaption>Figure 3</figcaption></figure><p>Next, the RANKX DAX function is used to calculate the total sales ranking by product brand.</p><p><strong>Ranking = RANKX(ALL(Products[product_brand]),[Total sales],,DESC,Dense)</strong></p><p>After calculating the ranks, we can use these values to identify the top-performing product brands. Figure 4 shows the brands along with their ranks.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/322/1*MmdHXLWhGtasOI0VElZWfA.png" /><figcaption>Figure 4</figcaption></figure><p>Next, we perform an additional calculation to filter the top-performing product brands according to the user’s selection.</p><p><strong>Top Performing Brands =</strong></p><p><strong>var user_selections=SELECTEDVALUE(‘Top Products’[Value])</strong></p><p><strong>var filtered_products=SWITCH(user_selections,</strong></p><p><strong>“TOP 3”,IF([Ranking]&lt;=3,[Total sales]),</strong></p><p><strong>“TOP 5”,IF([Ranking]&lt;=5,[Total sales]),</strong></p><p><strong>“TOP 10”,IF([Ranking]&lt;=10,[Total sales]),</strong></p><p><strong>[Total sales]</strong></p><p><strong>)</strong></p><p><strong>RETURN filtered_products</strong></p><p>Here, I used the <strong>SELECTEDVALUE </strong>DAX function to capture the user’s selection. I’ve also written a separate blog post that explains how to use the <strong>SELECTEDVALUE </strong>function in detail. (<a href="https://medium.com/@nilantha.sac/microsoft-power-bi-daily-challenges-14931ecb147b"><strong><em>check this link</em></strong></a>).</p><p>Next, we check the user’s selection and return the corresponding sales values.</p><p>The final result is shown in Figure 5</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/682/1*JZuHhYUpHBjr1UQyc1Tsig.png" /><figcaption>Figure 5</figcaption></figure><p>Sources:<a href="https://learn.microsoft.com/en-us/dax/">https://learn.microsoft.com/en-us/dax/</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4a713a9f4a0b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to set up a dynamic month name sorting axis]]></title>
            <link>https://medium.com/@nilantha.sac/how-to-set-up-a-dynamic-month-name-sorting-axis-c975569b5f92?source=rss-60fe5c3989fd------2</link>
            <guid isPermaLink="false">https://medium.com/p/c975569b5f92</guid>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[power-bi-solution]]></category>
            <category><![CDATA[dax]]></category>
            <dc:creator><![CDATA[Nilantha KM]]></dc:creator>
            <pubDate>Tue, 23 Sep 2025 19:00:46 GMT</pubDate>
            <atom:updated>2025-09-23T19:00:46.419Z</atom:updated>
            <content:encoded><![CDATA[<p>Suppose we have a chart as shown in Figure 1. The Y-axis is <strong>Total Sales</strong>, and the X-axis is <strong>Month Name</strong>. We have a requirement that the X-axis should start from April and end in March.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*a1eUL2xxgAYI48CBWWsYeA.png" /><figcaption>Figure 1</figcaption></figure><p>To achieve this, I will create a new column titled <strong>“New Month No”. </strong>Here is the DAX expression for the <strong>New Month No</strong>.</p><p><strong>New Month No = IF(‘Calendar’[Month No]&gt;3,’Calendar’[Month No]-3,’Calendar’[Month No]+9)</strong></p><p>Once we create the new column, April’s new month number becomes “1&quot; as shown in Figure 2.</p><p>Here, the first argument is <strong>‘Calendar’[Month No]&gt;3. </strong>Because April’s month number is 4. If the month number is 4, 4–3 becomes 1. That’s how April’s new month number becomes 1. Likewise, March’s month number is 3, then the else part is executed in the expression, and March’s new month number becomes 12(Figure 3) according to this argument ”<strong>Calendar’[Month No]+9</strong>”.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/897/1*xBC_f1DuXQvEKab35klObw.png" /><figcaption>Figure 2</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/921/1*sJvU8fLbVDMJXzJB9p1JIg.png" /><figcaption>Figure 3</figcaption></figure><p>Once all is done, we can sort by “<strong>New Month No</strong>”.<strong> </strong>In the future, if we want to change the sorting to start from May, we can easily achieve this by changing the DAX expression.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c975569b5f92" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Practical usage of SELECTEDVALUE DAX function]]></title>
            <link>https://medium.com/@nilantha.sac/practical-usage-of-selectedvalue-dax-function-5d25969a7c68?source=rss-60fe5c3989fd------2</link>
            <guid isPermaLink="false">https://medium.com/p/5d25969a7c68</guid>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[dax]]></category>
            <dc:creator><![CDATA[Nilantha KM]]></dc:creator>
            <pubDate>Tue, 23 Sep 2025 05:01:19 GMT</pubDate>
            <atom:updated>2025-09-23T05:04:25.216Z</atom:updated>
            <content:encoded><![CDATA[<p>Here is the official explanation of the SELECTEDVALUE DAX function</p><p><em>“Returns the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns alternateResult.”</em></p><p>Suppose we have a KPI visual and a slicer visual, as shown in Figure 1. By default, the card visual displays total sales(the reference data model is shown in Figure 2). We have a business need to display the “Total sales” of the supermarkets(Store type), not the Total sales of all store types.</p><p>To implement this, we can use the SELECTEDVALUE(<a href="https://learn.microsoft.com/en-us/dax/selectedvalue-function-dax">read more</a>) DAX function along with the CALCULATE function. It takes two arguments. First is the column name, and the second is the alternate value. In this scenario, the first argument is <strong>“store_type”.</strong>We can provide the “<strong>supermarket” </strong>as the second argument.</p><p>Here is the complete DAX.</p><p><strong>Default store type sales = CALCULATE([Total sales],Stores[store_type]=SELECTEDVALUE(Stores[store_type],”Supermarket”))</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/822/1*xjTD0wBEvWfkTzFXknrHzg.png" /><figcaption>Figure 1</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/639/1*wmmtBX7lHn70Y-qpGJmJqQ.png" /><figcaption>Figure 2</figcaption></figure><p>Resources:<a href="https://learn.microsoft.com/en-us/dax/selectedvalue-function-dax">https://learn.microsoft.com/en-us/dax/selectedvalue-function-dax</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5d25969a7c68" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to calculate the last N dates of sales in Power BI DAX]]></title>
            <link>https://medium.com/@nilantha.sac/microsoft-power-bi-daily-challenges-14931ecb147b?source=rss-60fe5c3989fd------2</link>
            <guid isPermaLink="false">https://medium.com/p/14931ecb147b</guid>
            <category><![CDATA[dax]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[power-bi-data-analyst]]></category>
            <dc:creator><![CDATA[Nilantha KM]]></dc:creator>
            <pubDate>Thu, 18 Sep 2025 06:28:34 GMT</pubDate>
            <atom:updated>2025-09-23T04:04:32.924Z</atom:updated>
            <content:encoded><![CDATA[<p>Suppose we have a Power BI data model as shown in Figure 1. And we need to calculate the last N dates of sales. The “<strong>Total sales”</strong> measure has been calculated using the calculate function, as shown</p><p><strong>Total sales = CALCULATE(SUMX(Sales,Sales[quantity]*RELATED(Products[product_cost])))</strong></p><p>There are many ways to calculate the last N dates of sales. However, the <strong>DATESINPERIOD(</strong><a href="https://learn.microsoft.com/en-us/dax/datesinperiod-function-dax"><strong>more</strong></a><strong>) </strong>DAX function is optimized for rolling windows, and this is the most efficient way to perform this calculation.</p><p><strong>Last N days Sales = CALCULATE([Total sales],DATESINPERIOD(‘Calendar’[date],MAX(‘Calendar’[date]),-10,DAY))</strong></p><p><strong>DATESINPERIOD </strong>takes four arguments.</p><p>The first argument is the Calendar table date. If we have current data in the table we can use the <strong>TODAY</strong> function as the second argument instead of <strong>MAX. </strong>The third argument is the number of days. Here, the last 10 days’ sales will be calculated.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qkQ0cOP3WbCV4-T6R1rQJA.png" /><figcaption>Figure 1</figcaption></figure><p>Sources: <a href="https://learn.microsoft.com/en-us/dax/datesinperiod-function-dax">https://learn.microsoft.com/en-us/dax/datesinperiod-function-dax</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=14931ecb147b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to calculate total revenue using an inactive relationship in DAX]]></title>
            <link>https://medium.com/@nilantha.sac/microsoft-power-bi-daily-challenges-c103677ef623?source=rss-60fe5c3989fd------2</link>
            <guid isPermaLink="false">https://medium.com/p/c103677ef623</guid>
            <category><![CDATA[power-bi-tutorials]]></category>
            <category><![CDATA[power-bi-training]]></category>
            <category><![CDATA[power-bi-data-analyst]]></category>
            <category><![CDATA[dax]]></category>
            <dc:creator><![CDATA[Nilantha KM]]></dc:creator>
            <pubDate>Thu, 18 Sep 2025 05:03:08 GMT</pubDate>
            <atom:updated>2025-09-23T04:05:20.518Z</atom:updated>
            <content:encoded><![CDATA[<p>Suppose I have a data model as shown in Figure 1. The <strong>Calendar </strong>table is the dimension table, and the <strong>Sales </strong>table is the fact table. Here, we can see two relationships.” date-&gt;stock_date” is an inactive relationship, and “date-&gt;transaction_date” is the active relationship.</p><p>We have a business need to calculate total revenue by <strong>stock_ date</strong>. In this case, we can use the <strong>USERELATIONSHIP(</strong><a href="https://learn.microsoft.com/en-us/dax/userelationship-function-dax">read more</a><strong>) DAX </strong>function. This function takes two arguments.</p><p>So we can complete the final DAX formula as below.</p><p>Total Revenue = SUM(Sales[product_retail_price])</p><p><strong>Revenue_by_stock_date = CALCULATE([Total Revenue],USERELATIONSHIP(‘Calendar’[date],Sales[stock_date]))</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/687/1*hdlgIHH6t-vx64zPbvn00A.png" /><figcaption>Figure 1</figcaption></figure><p>Sources:<a href="https://learn.microsoft.com/en-us/dax/userelationship-function-dax">https://learn.microsoft.com/en-us/dax/userelationship-function-dax</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c103677ef623" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>