Streamline Data Analysis with Excel Dynamic Arrays Feature

Streamline Data Analysis with Excel’s Dynamic Arrays Feature

In Excel 365 and Excel 2021 Microsoft has provided Excel users with some new features which will fundamentally change the way worksheets are designed. Dynamic array formulas allow you to work with multiple values at the same time in a formula.

Dynamic arrays solve some challenging difficulties in Excel and will be a feature which will make Excel users capable of building more streamlined Excel models which more effectively can help decision makers.

Dynamic arrays are resizable arrays that calculate automatically and return values into multiple cells based on a formula entered in a single cell.

Dynamic arrays are a new feature in Excel that allows you to work with arrays of data more efficiently and will reduce time spend on updating/changing analysis and Excel reports.

Dynamic array formulas return a set of values into neighbouring cells, also known as an array. This behaviour is called spilling.

Microsoft expands the list of dynamic array formulas frequently but when this blogpost was written the list included the formulas:

ARRAYTOTEXT, BYCOL, BYROW, CHOOSECOLS, CHOOSEROWS, DROP, EXPAND, FILTER, HSTACK, ISOMITTED, LAMBDA, LET, MAKEARRAY, MAP, RANDARRAY, REDUCE, SCAN, SEQUENCE, SORT, SORTBY, STOCKHISTORY, TAKE, TEXTAFTER, TEXTBEFORE, TEXTSPLIT, TOCOL, TOROW, UNIQUE, VALUETOTEXT, VSTACK, WRAPCOLS, WRAPROWS, XLOOKUP, and XMATCH.

Example 1 – SEQUENCE

The SEQUENCE function allows you to generate a list of sequential numbers in an array, such as 1, 2, 3, 4.

=SEQUENCE (rows, [columns], [start], [step])

=SEQUENCE(5) will return this array:

Streamline Data Analysis with Excel’s Dynamic Arrays Feature=SEQUENCE(5,5) will return this array:

Streamline Data Analysis with Excel’s Dynamic Arrays Feature

Start number and incremental steps can be entered as function arguments but in the above examples only number of rows and columns have been entered in the function.

Task: Build a dynamic Excel list which always shows expenses for the last two years starting from last day previous month and two years backward.

If the following is typed in the first cell (done 31/1/2024)

=TEXT(DATE(YEAR(TODAY())-2,SEQUENCE(24,1),1),”mmm-yyyy”)

It will result in:

Streamline Data Analysis with Excel’s Dynamic Arrays Feature

To make this example easier to understand will it be broken down in a couple of steps:

DATE(YEAR(TODAY())-2,SEQUENCE(24,1),1)

The DATE functions arguments are DATE(YEAR,MONTH,DAY).

In the DATE function’s year argument, the year has been extracted from the TODAY() (current date) minus 2 to go two years backward.

In the DATE function’s month argument, the SEQUENCE function has been told to generate and array with 24 rows and 1 column. In the DATE function’s day argument is just entered 1 to start from the first of the month.

=TEXT(DATE(YEAR(TODAY())-2,SEQUENCE(24,1),1),”mmm-yyyy”)

A TEXT function has been put around to tell Excel to return the date format “mmm-yyyy”.

Example 2 – UNIQUE

The UNIQUE function returns a list of unique values in a list or range.

=UNIQUE (array, [by_col], [exactly_once])

=UNIQUE(G5:G12) will return this array:

Streamline Data Analysis with Excel’s Dynamic Arrays Feature

=UNIQUE(G5:G12,,TRUE) will return this array (only distinct names):

Streamline Data Analysis with Excel’s Dynamic Arrays Feature

Example 3 – Best practice designing a worksheet for data analysis by using dynamic array formulas.

Dynamic array formulas can make you able to design Excel worksheets which are fully automated and self-cleaning. No more time spend when the next month data are available. No need to delete old data. No need to update calculations or formulas to include newly added data. The dynamic array formulas can take you to a completely new level of efficiency as an Excel user.

Task: Build a dynamic Excel list which always shows advertising stats, number of sales calls, and sales figures for the last three years starting from last day previous month and three years backward.

This company generates a list with monthly advertising expenses and number of sales calls their sales team has done. The sales records are broken down on day, product, and sales reps.

Streamline Data Analysis with Excel’s Dynamic Arrays Feature

The Excel analysis should provide the company with information about the correlation between sales and advertising expenses and sales calls.

In this example 3 dynamic array formulas are used.

The TAKE function returns a specified number of contiguous rows or columns from the start or end of an array.

=TAKE(array, rows,[columns])

The SORT function sorts the contents of a range or array.

=SORT(array,[sort_index],[sort_order],[by_col])

The SORTBY function sorts the contents of a range or array based on the values in a corresponding range or array.

=SORTBY(array,by_array,[sort_order],[array/order],…)

Step 1 – get the data from the source to the worksheet.

Both source lists are in tables. The list with advertising and sales calls is in a table named tblMarketing and the sales records in a table named tblSales.

To get the last 36-month dates from the tblMarketing table the TAKE function has been used.

=TAKE(tblMarketing[Date],-36)

The TAKE function has been told to create an array from the last 36 entries from the source table’s date column.

To make sure that it is always the dates from the last 36 month a SORT function has been nested inside the TAKE function.

SORT(tblMarketing[Date],,1)

The last argument in the SORT function is 1 to sort ascending.

All together the functions look like this:

=TAKE(SORT(tblMarketing[Date],,1),-36)

Streamline Data Analysis with Excel’s Dynamic Arrays Feature

Step 2 is to get the advertising and sales calls from the source to the destination list.

=TAKE(tblMarketing[[Advertising]:[Sales Calls]],-36,2)

Here the TAKE function has both columns in the array argument tblMarketing[[Advertising]:[Sales Calls]] and the last argument 2 tells the TAKE function to return a two column array and again the last 36 rows.

To take sure to get the last 36 month a SORTBY function is nested. The two columns need to be sorted by the Date column.

=TAKE(SORTBY(tblMarketing[[Advertising]:[Sales Calls]],tblMarketing[Date],1),-36,2)

Streamline Data Analysis with Excel’s Dynamic Arrays Feature

To be able to calculate the correlation the sales numbers also need to be brought in. Here is a SUMIFS function used.

Streamline Data Analysis with Excel’s Dynamic Arrays Feature

Summary

Dynamic Arrays are a huge change to Excel formulas and maybe the biggest change ever. This is a game changer for all Excel users from industries such as finance, healthcare, and retail, well from all industries. This can dramatically reduce time heavy tasks and make Excel users much more efficient. This blog post just scratches the surface of how this impact the way we can work with dynamic arrays in Excel.

Further reading

Improve Communication of Data Using Power BI Dashboards

How to Use Sparklines in Excel to Visualise Data Trends

Power BI – What is DAX?

Welcome to the world of data visualisation and analysis with Power BI! In this blog, we’ll dive into the heart of Power BI’s analytical capabilities: DAX measures.

DAX, or Data Analysis eXpressions, is a powerful formula language that enables users to define custom calculations in Power BI reports. Whether you’re a seasoned data analyst or just starting out, understanding DAX measures is key to unlocking insightful business intelligence. Power BI – What is DAX?

So, let’s explore some of the most popular measures, array functions, and time-intelligence functions, and see how they can transform raw data into meaningful insights.

Power BI – What is DAX?
Calculate Measure

Some often-used Power BI DAX measures include:

  1. SUM: calculates the sum of a selected column of data
  2. AVERAGE: calculates the average of a selected column of data
  3. COUNT: counts the number of rows in a selected column of data
  4. MIN/MAX: calculates the minimum or maximum value in a selected column of data
  5. DISTINCTCOUNT: counts the number of unique values in a selected column of data
  6. CALCULATE: modifies the context within which a DAX formula is evaluated
  7. FILTER: filters a table based on specific criteria
  8. RANKX: ranks values in a selected column of data
  9. YEAR/QUARTER/MONTH: extracts various time periods from a date column in a table

DAX measures allow users to perform complex calculations and analysis on their data quickly and effectively, providing valuable insights into trends and patterns in their data.

Array functions

Many measures in Power BI desktop need to be array measure, because of the data model structure in a Power BI model.

One of the powerful features in DAX is this ability to create array measures, which are measures that return an array of values instead of a single value. This can also be complicated for users, who haven’t been working with data cubs.

Here are some examples of DAX array measures:

1. DISTINCTCOUNT function:

DISTINCTCOUNT returns the number of distinct values in a column or expression. When used in an array formula, it returns a list of distinct counts for each value in another column.

For example, the following DAX formula returns an array of distinct count values for the “Product” column:

= DISTINCTCOUNT(Products[Product])

2. FILTER function:

FILTER returns a table that meets certain criteria specified in the expression. When used in an array formula, it returns a list of filtered tables for each value in another column.

For example, the following DAX formula returns an array of filtered tables for each value in the “Category” column:

= FILTER(Products, Products[Category] = EARLIER(Products[Category]))

3. VALUES function:

VALUES returns a table of unique values in a column or expression. When used in an array formula, it returns a list of unique values for each value in another column.

For example, the following DAX formula returns an array of unique values for each value in the “Category” column:

= VALUES(Products[Category])

4. SUMX function:

SUMX returns the sum of an expression for each row of a table. When used in an array formula, it returns a list of sum values for each value in another column.

For example, the following DAX formula returns an array of sum values for each value in the “Product” column:

= SUMX(Products, Products[Price])

Array measures are a powerful way to analyse data and gain insights from multiple dimensions at once. With DAX, you can build complex array formulas that can handle large amounts of data and answer specific business questions.

1. Sum of Sales by Year:

SUMX(

GROUPBY(Sales, Sales[Year], “SalesByYear”, SUM(Sales[Amount])),

SalesByYear

)

This formula groups sales by year and calculates the sum of sales amount for each year.

2. Average Sales per Customer:

AVERAGEX(

GROUPBY(Sales, Sales[CustomerID], “SalesByCustomer”, SUM(Sales[Amount])),

SalesByCustomer

)

This formula groups sales by customer ID and calculates the sum of sales amount for each customer. Then, it takes the average of all the customer sales.

3. Last Sale Date for Each Customer:

MAXX(

GROUPBY(Sales, Sales[CustomerID], “LastSaleByCustomer”, MAX(Sales[Date])),

LastSaleByCustomer

)

This formula groups sales by customer ID and finds the maximum date of sales for each customer, which represents the last sale date for that customer.

4. Sales Growth Rate by Month:

DIVIDE(

SUM(Sales[Amount]),

CALCULATE(SUM(Sales[Amount]), DATEADD(Sales[Date], -1, MONTH)),

BLANK()

) – 1

This formula calculates the growth rate of sales from the previous month. It uses the DIVIDE function to divide the total sales amount by the total sales amount from the previous month. Then, it subtracts 1 to get the growth rate percentage.

5. Running Total of Sales by Month:

CALCULATE(

SUM(Sales[Amount]),

FILTER(

ALL(Sales),

Sales[Date] <= MAX(Sales[Date])

)

)

This formula calculates the running total of sales by month. It uses the CALCULATE function to add up the sales amount for all dates that are less than or equal to the maximum date in the current filter context. The FILTER function is used to remove any filters on the date column that might interfere with the running total calculation.

Time-intelligence functions

DAX (Data Analysis Expressions) Time-intelligence functions allow users to analyse data over time, providing insights into key trends and patterns.

Some common DAX Time-intelligence functions include:

  1. TOTALYTD: This function returns the total value of a given measure from the beginning of the year up to the selected date.
  2. TOTALQTD: This function returns the total value of a given measure from the beginning of the quarter up to the selected date.
  3. TOTALMTD: This function returns the total value of a given measure from the beginning of the month up to the selected date.
  4. SAMEPERIODLASTYEAR: This function returns the total value of a given measure for the same period last year.
  5. DATESYTD: This function generates a table with all the dates within the current year up to the selected date.
  6. DATEQTD: This function generates a table with all the dates within the current quarter up to the selected date.
  7. DATEADD: This function adds a specified number of units to a given date.

Example: DATEADD Returns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates, in the current context.

Syntax: =DATEADD(<Dates>,<Number of intervals>,<Interval>)

where the <Interval> can either be Year, Month, Quarter or Day

Power BI – What is DAX?

These functions can be used in combination with other DAX functions to create more complex analyses and visualisations.

Conclusion

As we wrap up our exploration of DAX measures in Power BI, it’s clear that these tools are indispensable for any data professional. From calculating simple sums to performing complex time-based analyses, DAX measures empower us to make data-driven decisions with confidence.

We encourage you to experiment with the examples provided and discover the full potential of DAX in your own business scenarios. Remember, the power of data is at your fingertips, and with Power BI and DAX, there’s no limit to the insights you can uncover!

Further Reading

Power BI –Power Query M functions versus DAX

How Power BI Can Change Your Business