40% Off All Products

Black Friday and Cyber Monday Weeks

(through Dec 2, 2023)

Use coupon code BLACKCYBER23

Leverage Your Data Assets

and BI Investment

Use Microsoft Excel's Cube Functions to streamline year-end reporting

Sold in over 50 countries, Excelcraft's multi-media Training Kit, Excel Cube Formula Reporting Made Easy , now released in Third Edition , focuses on building cube formula reports and dashboards with data sourced from Excel Power Pivot / Power BI Data Models. Now includes bonus files and videos. Read more >

Using Excel's CUBESET function to present Year-to-Date reports

One approach to using the CUBESET() function to retrieve data for a range of months such as:

Year-to-Date
Quarter-to-Date
Any contiguous range of months

Using the CUBESET function to group together accounts presented in financial statements

This is a follow-on article to the one above. Read more >

GETPIVOTDATA vs. Cube Functions

A brief comparison of reports built with GETPIVOTDATA functions and those built with Excel Cube Functions. Read more >

Excel and Power Pivot for accountants and finance managers

Articles and links to articles describing how accountants, finance managers and finance professionals might leverage Power Pivot as a robust platform to handle a variety of financial reporting and analysis needs. Read more >

Calculation based on blank CUBEVALUE result returns #VALUE!

Error when CUBEVALUE function returns blank

If an Excel CUBEVALUE function returns a blank and is used in a subsequent calculation you may get a #VALUE! error. Read more >

Slicing and dicing text values in a PivotTable

From time to time you may want to retrieve text (as opposed to numeric) values into a PivotTable to slice-dice-pivot the text. Or you want to get properties/attributes of a dimension member in a PowerPivot model using Excel cube functions. Here is one solution.

How to capture a Slicer selection and how to use a Slicer to select a Measure

This video shows how you can capture the name of the item you have selected in a Slicer using only worksheet functions (when only one item has been selected). You could, for example, use the name in your report title. It also shows how you can use a Slicer to select a Measure that will be displayed in a cube formula report.

Business Modeling and Planning with Power Pivot

How you can add assumptions and modeling capabilities to you Power Pivot Data Model. Read more >

Flexible cash flow forecasting

This article explains one way to forecast cash flows using assumptions on Creditor and Debtor Days , that are more fine-grained, such as 25 days or 44 days. Read more >

For Accountants who present
Financial Statements in Excel

Harness the power of Excel's Data Model

This multi-media Training Kit is designed for anyone tasked with importing downloads from an accounting system and presenting financial statements in Excel. Read more >

Presenting quarterly financials with Excel's Data Model

If you already present monthly financial statements using Excel's cube functions, this video provides some suggestions for building quarterly financial statements.

How to create hover-over images in Excel

This 2 minute video explains how.

Cube Functions, one of Excel's best kept secrets ...

Click here for an overview.

Eliminate VLOOKUPS from your data analysis

If you find yourself using VLOOKUP functions to translate codes (customer codes, product codes and the like) in your data tables into recognizable names, here is a better approach. Watch this video or Read more>

Comparing Actual and Budget with a Scenario table

In certain circumstances you may find it useful to set up Actual and Budget data (perhaps multiple flavors of Budget) in your Data Model using a Scenario table. Doing so reduces the number of time calculation measures that you need to create (MTD, QTD, YTD etc). This video shows how you might do this. You can download the workbook used in the video here .

Product and Customer Profitability

Gross Profit analysis by product or customer may not be too challenging for your existing accounting or ERP systems. Net Profitability analysis is another story. This article considers an approach to allocating indirect costs and overheads in an Excel Data Model, to arrive at Net Profit by Product.

Data modeling the Data Model

Analyzing Data with Power BI and Power Pivot for Excel

I have recently read this book (published in 2017) by Ferrari and Russo (aka "The Italians") of sqlbi.com and found it extremely informative with a very practical focus. To analyze data that resides in, or comes from more than one table, you will frequently need to model the data regardless of the technology platform you are using. This book describes how to model data for a variety of analytical scenarios. From a generic data modeling viewpoint, the book appears to incorporate aspects of the Kimball Techniques but does so in the context of the workings of the Excel/Power BI Data Model. A common thread is the focus on creating your model so that the use of complex DAX calculations is substantially minimized.

This is not a review of the book and I have no vested interest in promoting it. But if you are working with the Excel/Power BI Data Model and unsure how best to build your model to meet your analysis needs, you might find this book both useful and readable.

Excel reports that combine data from Power Pivot and Power BI

Let's say that you have some budget data in an Excel Data Model. In Power BI (PBI) the corresponding actual historical data has been published by your finance department. You want to conduct some performance analysis between the two data sets. Read more >

Terms of Use

Privacy

Created with

Excelcraft