Today, I am going to show you how to bring/join the rows of previous partition with the current partition in DAX, with the metrics for the previous rows in the current time frame. From the above image you can see there are 2 Customers in 2021 that are brought forward into 2022 with their Sales in 2021 and Sales in 2022. First order of business is to get TOPN Customers in each year and for that we can use TOPN paired with GENERATE or with WINDOW VAR N = 2 VAR TopCustomersEvery

Here is how to insert blank rows after each group, sample data: OrderID OrderDate Product Category Quantity NetPrice 1001 2025-01-01 Laptop Electronics 1 60000 1002 2025-01-02 Smartphone Electronics 2 42000 1003 2025-01-03 Headphones Accessories 3 4200 1004 2025-01-04 Laptop Electronics 1 65000 1005 2025-01-05 Smartphone Electronics 1 21000 1012 2025-01-12 Headphones Accessories 4 5500 With Excel's Dynamic Array Functions: =LET( Table, Sales, u, UNIQUE(Sales[Product]), n, 1,

In an earlier blog post I showed how to implement Fill Up and Fill Down in DAX that relied heavily on using Calculated Columns. However now that I have more experience with WINDOW functions I can show you a solution that does all of that with just measures. But first I will introduce a simpler dataset that makes it easier to consume. The table contains only 3 columns. These 3 columns together do not make up unique combination, the row (2025-02-01, null, null) is present twice

In Power BI if you create a relationship between a Date and DateTime column then you will see that the relationship doesn't work or if it works then it only works for time that is 12:00AM / Midnight. You can overcome this limitation by changing the Join On Date Behavior property in Tabular Editor. First I have created a DateTime version of OrderDate in Sales table createOrReplace ref table Sales column OrderDateTime = Sales[OrderDate] + ROUND ( RAND(), 2 ) formatStri

DAX has an interesting function named NONVISUAL, which marks a Filter in SUMMARIZECOLUMNS to impact only the grouping columns and not affect the measures. We have model where there are Years in the Dates[Year] from 2020 - 2026 but the Sales only occured for 2021 - 2025, so when a user writes a DAX Query like the following one it returns 5. DEFINE MEASURE Sales[#Years in Dates] = COUNTROWS ( ALLSELECTED ( Dates[Year] ) ) EVALUATE SUMMARIZECOLUMNS ( ProductCategory[C

In this blog let's take a look at how to use DAX's UDF in a DAX Query that mixes TABLE, COLUMN, MEASURE, and FUNCTION keywords. We start with a UDF that returns Dates[Year] by the position that we pass, the focus is primarily on First and Last values when there are Years in Dates table that do not have a row in Sales table. Following UDF code returns First and Last Years: DEFINE FUNCTION FxGetValueByPosition = ( N: INT64 VAL, OrderByCol: ANYREF EXPR, SortOrder: ANYREF EXPR )

Let's say you want Top 2 Colors of each Brand, how would you do that? Here are different ways of computing that. Using INDEX & UNION DEFINE VAR Temp = ADDCOLUMNS ( ALL ( Products[Brand], Products[Color] ), "@Sales", [Sales Amount] ) VAR Uno = INDEX ( 1, Temp, ORDERBY ( [@Sales], DESC ), PARTITIONBY ( Products[Brand] ) ) VAR Dos = INDEX ( 2, Temp, ORDERBY ( [@Sales], DESC ), PARTITIONBY ( Products[Brand] ) ) EVALUATE UNION ( Uno, D

Today I’m going to show you an application I built with the help of ChatGPT that generates large volumes of Sales data based on the Microsoft's ContosoRetailDW model.

Today, I am going to explain how to show values in a Power BI visual but only for the last month or last N months.