To use the Window function, we can start by creating a virtual table of customer sales by sales key using the CALCULATETABLE function and then creating a window on the table to return only the results we want by position. We can make use of DAX query to do this.
As you can see from the code above, we start off by creating a table in a variable called CustomerSales, which has a column for CustomerKey and a summarized sales column. The table is filtered, so that the customer is not blank.
We then create a second variable called ‘CustomerSalesWindow’ to filter our CustomerSales table using the Window function. The initial customer sales table is as follows
We then create the window using the following code:
As you can see the original table is sorted by sales in descending order (we can also use partition, but not In this example).
The syntax for the window function is as follows: WINDOW ( from[, from_type], to[, to_type][, <relation> or <axis>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>][, <reset>] )
So you can see that we have: WINDOW( from: 1 from-type: ABS to: 1 to-type: ABS relation: CustomerSales ORDERBY([Sales[, DESC))
As you can see the ORDERBY and PARTITION are very similar to SQL in that it is sorting the sales field in descending order. The positions from ABS: 1 to ABS: 3 give us the top 3 sales results.
Of course, we could have used something like TOPN() function to get the top 3 sales, but if we wanted a specific location e.g. The 3rd to 5th positions, then the WINDOW() function would make it much easier.
The OFFSET() Function
The OFFSET() function ‘Returns a single row that is positioned either before or after the current row within the same table, by a given offset’.
In this example, we use OFFSET() to display the previous month’s sales in a table. We start off by creating a _SummarySales table and then add a second column _SalesOffest, which uses -1 as the delta which shows the previous month’s sales when the ORDERBY clause on CalanderMonth is set to ascending order (ASC). We can then add the final SalesMonthOnMonth column to the table to get the final summary.
AFDEFINE-- Syntax:--OFFSET ( <delta>[, <relation> or <axis>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>][, <reset>] ) -- Create Sales by moth table VAR _SummarySales = SUMMARIZECOLUMNS( DimDate[CalendarMonth], "Sales", SUMX( RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount] ) )-- Add the offset column by one month VAR _SalesOffset = ADDCOLUMNS( _SummarySales, "PreviousMonth", SELECTCOLUMNS( OFFSET( -1, _SummarySales, ORDERBY( [CalendarMonth], ASC ) ), [Sales] ) )-- Add a month on month difference column VAR _SalesMonthOnMonth = ADDCOLUMNS( _SalesOffset, "Month on Month", [Sales] - [PreviousMonth] )EVALUATE _SalesMonthOnMonth
After adding the previous month column, we can then add a month-on-month column. This gives us a table as follows:
The application of various filter arguments is crucial to controlling the output of DAX measures when visual filters are present on a report page. When CALCULATE() is used in an expression, any filters applied will override any existing filters on the filter being applied. e.g. CALCULATE(SUM(V_FactOnlineSales[SalesAmount]), V_DimProduct[BrandName]=”Litware”) Any filter on V_DimProduct[BrandName]is removed and the new filter is applied. So a slicer on that column would stop working.
In this example, we create a number of measures in a matrix, using different filter functions. There are 3 slicers that can be applied to the matrix: Year, BrandName, and ProductCategoryName. The original simple measure is sales amount, which is simply just a sum of sales amount from the FactOnlineSales table from the Contoso Retail Datawarehouse.
Simple SUMX Measure: The original measure with no filter arguments, will just be affected by the current filter context applied by both slicers and matrix or table rows and columns Sales Amount = SUM(V_FactOnlineSales[SalesAmount])
CALCULATE(): Using the powerful Calculate() function, we can apply filters to the original measure to change the way the visual filters affect the measure.
In the next example, we apply a filter using calculate, but on the year, which is a filter in the matrix. As you can see, the filter on year is removed from the columns and the sum of sales for 2008 is repeated for each year from 2007 to 2009.
Sales Amount (Filter Year 2008) = CALCULATE(SUM(V_FactOnlineSales[SalesAmount]), V_DimDate[CalendarYear]=2008).
To fix this issue we can use KEEPFILTERS() KEEPFILTERS(): The KEEPFILTERS() function helps us keep the filters on Year in the matrix: Sales Amount (KeepFilters) =
REMOVEFILTERS(): By using the REMOVEFILTERS() function along with CALCULATE() we can remove all the filters applied to the report. As you can see in the table above, this removes the filters from the Year, Brand, and Product Category Name columns. Essentially giving us the total sales for the company for all time.
Sales Amount (RemoveFilters: all) = CALCULATE(SUMX(V_FactOnlineSales, V_FactOnlineSales[SalesAmount]), REMOVEFILTERS()) REMOVEFILTERS(‘TableName’): We can also use the REMOVEFILTERS() function to remove filters only on a specific table (not all tables). In this example, we remove any filters on the V_DimProduct table. Other filters will continue to filter the measure:
REMOVEFILTERS(‘TableName'[ColumnName]): A more granular method is to remove filters just on specific columns. In this example, we remove any filtering applied by V_DimProductCategory'[ProductCategoryName]
ALL(‘ColumnName’): Remove all filters from specified columns:
ALLEXCEPT(): Remove all filters except the specified column or columns. In this example, we remove all filters except from the V_DimProductCategory'[ProductCategoryName] column.
ALLNONBLANKROWS(): If your data has blank rows in it, then this may be useful to you. If it doesn’t then you don’t need it. You can apply it to a table or columns:
ALLSELECTED(‘TableName’): Removes filters coming from within the visual. So in this example, ALLSELECTED() removes the filters on year within the matrix i.e the columns, but if you use the slicer on year, it will still work. You can also apply to columns.
Sales Amount (AllSelected DimDate) = CALCULATE(SUMX(V_FactOnlineSales, V_FactOnlineSales[SalesAmount]), ALLSELECTED(V_DimDate)) Using multiple Tables and Columns in Filter Expressions
The above use of filter functions affecting visuals can be further expanded to include multiple tables and multiple columns as in the examples below:
It may be useful to create templates for creating DAX models, using commonly used DAX measures. Here we create a template for commonly used sales measures, such as Sales YTD, Sales Last Year, Sales Year on Year, and Sales Year on Year %, we can then apply the same logic for QTD and MTD.
The full code for creating the measures, I will add to the bottom of this page.
For this example, I am using just 2 tables from the Contoso Retail Data warehouse: DimDate and FactSales. They are joined on the DateKey.
We start with the Year sales measures as shown below in the DAX query editor. To add them to the model, we just click ‘Update model: Add new measure’, but first we want to format the code, using the Format Query button.
Here is the code, with the DAX formatted.
We can then click the 4 ‘Update mode: Add new measure’ texts and it will add the 4 measures to the model..
We can then create similar measures for QTD and MTD as follows:
Here is the code for the Quarterly measures:
The code for creating the Monthly measures is as follows:
That gives me the 12 measures in record time!
As promised here is the full code that can be copied and pasted. Of course, you’ll need to change the table names as required. Note, I have created an empty ‘_Meusures’ table to act as a container for the measures.
Demonstrating the use of the Index function with the Contoso Retail Data warehouse. We can start off by building a virtual table in the DAX query editor, which we can use to apply the Index function. The table creates is a list of the first 10 customers by customer key from the DimCustomer table.
If we evaluate the CustomerSalesSample table first we can see the table we are working with.
The syntax for the INDEX function is as follows:
— INDEX(<position>[, <relation> or <axis>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>][, <reset>] ) In our example, we use the <position> of 1 to get the first sales amount, which is the highest sales amount as the Total Sales column is sorted in descending order (DESC)
And then when we evaluate the Index expression (variable Ind), we get the following, which is the correct output we are looking for.
To get the last position, we could either sort the data by ascending order (ASC) or we can use the INDEX of -1 as the following example:
When we use -1 as the index we get the blank sales returned, which isn’t what we wanted, so need to modify the code.
One way of filtering out the blanks from the sales table is to add a filter on the table to filter out the blanks as an additional variable as int he below.
DEFINE--Syntax--INDEX(<position>[, <relation> or <axis>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>][, <reset>] ) VAR CustomerSalesSample = CALCULATETABLE( SELECTCOLUMNS( DimCustomer, "CustomerKey", DimCustomer[CustomerKey], "FirstName", DimCustomer[FirstName], "LastName", DimCustomer[LastName], "Total Sales", SUMX( RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount] ) ), TOPN( 10, DimCustomer, DimCustomer[CustomerKey], ASC ) ) VAR CustomerSalesNotBlank = FILTER(CustomerSalesSample, NOT(ISBLANK([Total Sales]))) VAR Ind = INDEX( -1, CustomerSalesNotBlank, ORDERBY( [Total Sales], DESC ) )EVALUATE Ind
Evaluating this code now gives us the least total sales amount that is not blank:
To demonstrate the use of the DAX function, we’ll start by creating a simple sales table with the sales amount and the year. Then we’ll add additional columns to create examples of using the RANK function.
In the first example (SimpleSalesRank), we’ll just create a simple ranking. The default function ranks the Sales column from the least amount to the highest amount and you’ll notice the blank sales value is included as rank one. Note, we can order by more than one column if partitioning by more than one column.
SimpleSalesRank=RANK(ORDERBY(SalesRank[Sales]))
The first thing we can do is move the blank value to the end of the ranking, using the LAST parameter.
In the next example, we create a similar Sales table with a Sales column and a year column. We can then use the RANK functions to create our rankings.
First, we’ll create a simple ranking as before, but with RANKX(). Included here is the function syntax in the comments. As you can see from the table above, the RANKX function defaults to sorting the highest sales value first, whereas the RANK function sorts it last. The RANKX function also defaults to putting the blank last, whereas the RANK function ordered it first.
We can also apply the DENSE clause for the ties, as the default is to SKIP the ranking when there have been equal values, for example, there are 2 sales of value 94, which are both ranked 6, as the default treatment of ties is set to SKIP, the next rank value jumps to 8. With the DENSE clause, the next rank does not jump.
RankXFunctionDENSE=--RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]]) --note: defaul ties is SKIPRANKX(SalesRankX2, SalesRankX2[Sales], , ASC, DENSE)
In the next example we use RANKX with RELATEDTABLE(). We start of by creating a sample of the DimCustomer table, joining it to the FactOnline Sales table and then adding a ranking table to the new customer table for total sales. We then check the ranking by adding a totalsales
Step 1: Create a sample customer table. In the core below, we create a simple selection of CustomerKey, FirstName, LastName and then filter the table by the first 10 customers by customerkey. So this will give us a table of the first 10 customers in the DimCustomer table. These data come from the Contoso Retail Data Warehouse.
The table below is created (first 3 columns), then we add the SalesRank and CustomerTotalSalesCheck to demonstrate the use of the RANKX function with RELATEDTABLE function
The code for ranking for sales is below. Then we add the CustoemrTotalSalesCheck to confirm that our ranking is working correctly. As you can see it is. The first rank of 1 is allocated to total sales of £3,932. The last value is blank ranked 10. You will notice there is no rank 8, so the default tie is to SKIP rank when then is a tie. We can change this by adding the DENSE clause
Creating a Measure using RANKX() Now we have some good examples of using the RANK and RANKX function we can use them in a measure, which creates a temporary table in memory.
To create the measure we can use the DAX Editor in Power BI, DAX Tabular Editor, or DAX Studio. My preference is the DAX Tabular editor, but the code will work in all three, allowing us to see the data as we build the measure. If you just try and build the measure, you can’t be sure what’s going on unless you build the table directly in Power BI, but they are slower to run.
Here we borrow the code from the previous example creating a physical table, but ensure the table is set to DimCustomer in the Rank function.
We should now be able to use the DAX in a measure, so we transfer the DAX code into a Measure as follows: The code using DEFINE and EVALUATE is a DAX query in the DAX query editor. When we create a measure we are creating a DAX expression. DAX expressions are converted to DAX queries when they are evaluated. Here is the code for creating the measure below.
Running Total = VAR MaxDateInFilterContext = MAX ( Dates[Date] ) //variable 1 max date# VAR MaxYear = YEAR ( MaxDateInFilterContext ) //variable 2 year of max date VAR DatesLessThanMaxDate = //variable 3 filter dates > variable 1 and variable 2 FILTER ( ALL ( Dates[Date], Dates[Calendar Year Number] ), Dates[Date] <= MaxDateInFilterContext && Dates[Calendar Year Number] = MaxYear ) VAR Result = //variable 4 total sales filtered by variable 3 CALCULATE ( [Total Sales], DatesLessThanMaxDate ) RETURN Result //return variable 4
eg. if matrix is filtered, IF(ISFILTERED(field], SELECTEDVALUE([column])
HASONEVALUE: Check if column has one value in if Valuecheck = if(HASONEVALUE([column], VALUES(field))
FILTER table by related field = united states and sumx salesamount_usd = SUMX(FILTER( ‘InternetSales_USD’ , RELATED(‘SalesTerritory'[SalesTerritoryCountry]) <>”United States” ) ,’InternetSales_USD'[SalesAmount_USD]) AND, can also use && Demand = SUMX ( FILTER ( RELATEDTABLE ( Assignments ), AND ( [AssignmentStartDate] <= [TimeByDay], [TimeByDay] <= [AssignmentFinishDate] ) ), Assignments[Av Per Day] )
CALCULATETABLE, SUMMARIZE Calculate Table with Summarize and Filter
Order Profile = CALCULATETABLE ( SUMMARIZE ( ‘Sales Table’, ‘Sales Table'[Order_Num_Key], Customer[Sector], “Total Value”, SUM ( ‘Sales Table'[Net Invoice Value] ), “Order Count”, DISTINCTCOUNT ( ‘Sales Table'[Order_Num_Key] ) ), YEAR ( DimDate[Datekey] ) = YEAR ( TODAY () ) )
)
USERELATIONSHIP Uses inactive relationship between tables
An example we start of we a sales and date table to crate our running table sales from. We start by creating a sales measure as follows:
Sales = SUM(FactSales[SalesAmount])
Next we create a quick measure to create the ;Running Total in Sales’ and add it to our table:
The Code for the Quick Measure ‘Sales running total in Year’ is a follows:
Sales running total in Year =
CALCULATE(
[Sales],
FILTER(
ALLSELECTED( 'Date'[Year]),
ISONORAFTER( 'Date'[Year], MAX('Date'[Year]), DESC
)
))
It looks pretty simple, but there is quite a lot to it, so let’s break it down:
1. CALCULATE() Syntax: CALCULATE(Expression, [Filter1]…) The CALCULATE() function takes the [Sales] measure as the first argument. [Sales] is just the sum of sales.
Next, we have the main filter argument that filters the [Sales] measure on a range of years from the ‘Date’Year column. The filter starts with the FILTER function which takes ALLSELECTED(‘Date'[Year]) as the column to filter on and the ISONORAFTER( ‘Date'[Year], MAX(‘Date'[Year]), DESC as the filter argument.
ALLSELECTED(‘Date'[Year]) The ALLSELECTED function removes all filters external to the column ‘Date'[Year], so in our table, the Years in the year column will be ignored, so all years in the ‘Date’ table will be included in the column. We can prove this by creating another measure:
When added to our table visual, we can see the count is 24 for every year row in the table. That is because this particular table has 24 years of date information in it.
The next part is the tricky part, how we filter ALLSELECTED(‘Date'[Year])
ISONORAFTER(‘Date'[Year], MAX(‘Date'[Year]), DESC) The ISONORAFTER function compares each element of the first argument (the ‘Date'[Year column] with the second argument, the MAX(‘Date'[Year]), which is either sorted in ascending order (ASC) or descending order (DESC). We can check what MAX(‘Date'[Year]) evaluates to with the following measure:
Max Date Year = MAX(‘Date'[Year])
We can see in our table that the max date equals the Year in the table visual.
4.2 The second argument is MAX(‘Date'[Year]). To understand what this function does, we can create a measure as it may not be what you think.
Max Date Year = MAX(‘Date'[Year])
As you can see in the table below, the Max Date Year used in this context actually returns the same date as the Year column.
The comparison works as follows:
‘Based on the sort order, the first parameter is compared with the second parameter. If the sort order is ascending, the comparison to be done is the first parameter greater than the second parameter. If the sort order is descending, the comparison to be done is the second parameter less than the first parameter’
To table below shows what is going on (if I filter the page to years 2007-2009), which effectively filters the years in the table down from 24 to 3, we can create the following table. Essentially it is checking if each element in the date year table is on or after the max date, which is derived from the year in the table visual. If it is less than the Max([Date] then it returns true.
The Years in the Date[Year] column are then returned as a filter to the Calculate to filter the [Sales] measure creating the ‘Running Total in Sales’
Year: 2007
Date[Year]
Max([Date])
ISONORAFTER(a,a, DESC) If first less than second
Sales Total
2007
2007
TRUE
4.56M
2008
2007
FALSE
2009
2007
FALSE
Running Total
4.56M
Year: 2008
Max([Date])
ISONORAFTER()
Sales Total
2007
2008
TRUE
4.56M
2008
2008
TRUE
4.11M
2009
2008
FALSE
Running Total
8.63M
Year :2009
Max([Date])
ISONORAFTER()
Sales Total
2007
2009
TRUE
3.74M
2008
2009
TRUE
4.11M
2009
2009
TRUE
3.74M
Running Total
12.41M
That’s pretty complicated if you ask me, even though it is correct. We can create our own Running Total in Sales Measure, that is easy to understand. The code is below:
Running Total human =
VAR the = SELECTEDVALUE('Date'[Year])
VAR MaxDateInFilterContext = MAX ('Date'[Year])
VAR DatesLessThanMaxDate =
CALCULATE([Sales], 'Date'[Year]<= MaxDateInFilterContext)
RETURN
DatesLessThanMaxDate
If you look around the DAX functions, you are going to be disappointed. There is no week-to-date function like there is in Tableau. There are different methods you can google, but let’s create our one here from scratch. In this example, we are using the FactOnlineSales table from the Contoso Retail DW. Before we start, want can as an option bring the old data into the future, by using the method outlined here. This is not a requirement, but it means we can create live-looking scenarios with sales data up until yesterday.
To verify the measure we are going to create, we can create a fixed date range measure that we can compare our dynamic measure to:
This measure uses the DATESBETWEEN() function as the filter for the CALCULATE() function. Note, that sales will be inclusive from the start to the end dates. You can change the date range to reflect the current week you are in. The dates used in this example are reflective of the time when I created the function.
To create a dynamic measure, we can start by adding a new measure OnlineSales WTD (week to date). Let’s start by adding in the TODAY() function
OnlineSales WTD = TODAY()-1 We are going to use the TODAY()-1 as we usually want the data up until the end of the previous day and not to show any partial data for the current day that may be imported to the database. If we add it to a card visual, we can verify the output as today-1 (at the time of writing):
Next, we can use the WEEKDAY function to get the day number from today. We also use variables here, to tidy up the code:
OnlineSales WTD = VAR ThisDay = TODAY()-1 VAR ThisDayNum = WEEKDAY(currentday) RETURN currentdayNum
The measure now displays 6. Today is the 16th of November 2023 (at the time of writing), which is a Saturday, but we added the -1, which is Friday, so we want it to display 5. To get the WEEKDAY() function to start on a Monday, we can add 2 as the second argument, which sets the first day of the week (1) to Monday and the last day of the week to Sunday (7). You can change this according to your needs.
OnlineSales WTD = VAR ThisDay = TODAY()-1 VAR ThisDayNum = WEEKDAY(currentday, 2) RETURN ThisDayNum
This now gives us 5, which is what we want:
Next, we want to calculate the first day of the week, which we can do as follows:
OnlineSales WTD = VAR ThisDay = TODAY()-1 VAR ThisDayNum = WEEKDAY(currentday, 2) VAR FirstDayOfWeek = thisday -(thisdayNum-1) RETURN FirstDayOfWeek
So now we have the logic for calculating the first day of the week and today. So we want to calculate the sales between these 2 dates. So let’s try and use this logic to create a dynamic WTD measure:
OnlineSales WTD = VAR ThisDay = TODAY()-1 VAR ThisDayNum = WEEKDAY(Thisday,2) VAR FirstDayOfWeek = Thisday -(ThisDayNum-1) VAR MonSales = CALCULATE(SUM(FactOnlineSales[SalesAmount]), DATESBETWEEN('Date'[Date], ThisDay, ThisDay)) VAR WTDSales = CALCULATE(SUM(FactOnlineSales[SalesAmount]), DATESBETWEEN('Date'[Date], FirstDayOfWeek, ThisDay)) RETURN IF(ThisDayNum=1, MonSales, IF(ThisDayNum>1, WTDSales))
The code uses two different calculations for WTD. If the previous day is a Monday, it just calculates the Monday sales, if it is after Monday, it calculates the WTD sales
Now let’s say we want to go back to any point in time and get the Week to Date sales, we can tweak the code as follows:
OnlineSales WTD = VAR ThisDay = MAX('Date'[Date])-1 VAR ThisDayNum = WEEKDAY(Thisday,2) VAR FirstDayOfWeek = Thisday -(ThisDayNum-1) VAR MonSales = CALCULATE(SUM(FactOnlineSales[SalesAmount]), DATESBETWEEN('Date'[Date], ThisDay, ThisDay)) VAR WTDSales = CALCULATE(SUM(FactOnlineSales[SalesAmount]), DATESBETWEEN('Date'[Date], FirstDayOfWeek, ThisDay)) RETURN IF(ThisDayNum=1, MonSales, IF(ThisDayNum>1, WTDSales))
Now if we add the day filter and select today’s date, the week-to-date measure should still match the check, but we can also go back to any point in time and calculate week-to-date.
Lastly, to calculate the previous year week to date sales, we can create a measure based on our existing OnlineSales WTD measure, but use the SAMEPERIODLASTYEAR() function, to calculate the previous year’s week-to-date, without having to re-rewrite all the code.
OnlineSales WTD Prev Year = CALCULATE([OnlineSales WTD], SAMEPERIODLASTYEAR('Date'[Date]))
