% of change since first month of the year using DAX and Power Pivot

Today we are solving a problem that I got from a Microsoft financial business users, she wanted to see the percentage change for every month of the fiscal year compared to the first month of that fiscal year. So lets go and see how we can solve this using DAX.

Read more »


Manipulating a date column (aka remember time intelligence functions)

Now that my book is wrapping up I finally have a bit more time to do some blogging. I have quite the backlog :). A quick one today. I got this question today:

Why does my function DATEADD not return what I expect? It gives me empty results on some row??


So what is happening here? He is using the DATEADD function to move back to the value of Table1[Date] 14 days previous to the value for the current row. Remember DATEADD is a time intelligence function, those have special rules, one of them is that the the dates need to be in a contiguous date range, there cannot be a missing dates in the middle. If you do miss dates you will get unexpected results, in the case above the date “12/7/2013” did not exist as a value in the Table1[Date] column, thus will return empty results.

What do you need to do? two options:

1 Create a separate date table containing a continuous date range for all values you want to report on, this will solve the “missing values”

2 Don’t use time intelligence functions, in this case you could have done a simple “=Table1[Date]-14”. That DAX expression simply uses the value for each row in the table to create a new date value. It doesn’t need to lookup a value in the column, it can work off its own value.


The power of Analytics, “Big data” and the NBA

This blog on Grantland is just too good not to share. It provides a great insight into what the world of data can do for pro sports. For example this chart based on data from a game shows the model estimating what is most likely to happen next, and what changes in EPV (expected possession value) will result from that particular action:

Read this great article here



Book “Dashboarding and Reporting with Power Pivot and Excel” coming to you this spring!

I am really happy to announce that I am working on a book called “Dashboarding and Reporting with Power Pivot and Excel: How to Design and Create a Financial Dashboard with PowerPivot – End to End” it will release on April 1st with Bill Jelen’s Holy Macro! Books.

The book is written for the user who wants to get started with Dashboarding and reporting using Power Pivot and Excel. This book will not cover all the features in Power Pivot or Excel and DAX. there are already some great books out there that take care of that.

The book we will provides a practical step by step guide on creating a financial dashboard. The book covers in detail how to combine and shape the relevant data, build the dashboard in Excel, providing layout and design tips and tricks, prepare the model to work with fiscal dates, and show values used in many financial reports, including year-to-date, variance-to-target, percentage-of-total, and running totals reports. In the book we will follow Jim the business analyst on his journey from importing data, preparing the data into the model, creating the dashboard to sharing the end result on SharePoint and Power BI.

The book will read as a story that offers the reader a practical, real-world scenario and can be used as a day-to-day reference. Though the guide focuses on features that can be used in both Power Pivot for Excel 2010 and 2013, a chapter that discusses Power View—compatible with Excel 2013— and Power BI is also included.

It’s already available for pre-order on Amazon here.


System Center Management Pack for SQL Server 2012 Analysis Services

Great news, Microsoft just released the System Center Management Pack for SQL Server 2012 Analysis Services. What is in it:

System Center Operations Manager (SCOM) is a cross-platform data center management system that provides health and performance monitoring of systems and services. This management pack extends SCOM to include Analysis services monitoring capabilities. BI Management packs have been a constant redzone ASK from CSS and the external community, and the AS management pack is the first MP delivered from the BI MP suite.

These management packs offer the following benefits

• Simplified experience for discovery of Analysis Services Instances ( 2008 [Multidimensional, PowerPivot], 2012 [Multidimensional, PowerPivot, Tabular])

• Performance monitoring for Instances, Databases and Partitions

• Space monitoring for Instances, Databases and Partitions

• Extensive performance collection for memory, CPU and I/O utilization

• Ability to define custom thresholds for each monitor to configure warning and critical alerts

• Detailed knowledge to guide the IT operator in resolving problems

Download it here.


Change the language of your Office 365 site

As I have been playing with Power BI a lot lately (surprise Smile) there was one thing that took me some time to figure out. I wanted to change the language of my site to hebrew (I wanted to see how SharePoint behaves in certain places). It took some time to figure out how this works. You need to change the language in two different places. You need to start by enabling your site to a specific language, open you site (you need to have admin rights on this site): image

Click on Site Contents:


Now click on settings, here you can change a lot of different things:


The thing I am interested in is the Language setting. Here I select any alternate language that my site supports, in this case I chose Dutch (much safer than Hebrew for me Smile, last time I had a hard time getting English back)


And press OK on the bottom. Now the interesting bit here is that the site hasn’t changed at all .. We have just allowed the visitors of the site to choose this language. Now lets go set the language for me. I select my name in the top right and select about me:


In my profile I click on edit profile:


Here I want to select the language settings but it’s a little hidden away, click on the … dots and select “Language and Region: image

This opens the details where I can change the language:


Here I click add and add Dutch as my first language:


Now I press “Save all and close”. Now I want to go back to the site, it will take a minute or so for the setting to work: image

The same will also work for you Power BI site.


Show facts in different measurements using DAX and PowerPivot

Yesterday got a question where a customer pulled a raw fact table into a Tabular model, the customer would like to take a look at all the orders by the customer in different measurements. The multiple measurements are stored in the Material Master table, so if the customer wishes to report items in Cases, Pallets, Cubic meters, etc they can do so.

This straight forward in DAX but I still decided to do a blog post instead of answering in an email as this might help others.

Lets say I have the following two tables:


One is the fact table with products of a certain color in stock based on a certain measurement. I also have a table that describes each measurement and the ratio to calculate the measurement to Cubic Metrics. After loading these into Power Pivot I DO NOT create a relationship between the two tables.

The first thing that I want to do is create a unified stock value that is all based on a single measurement. I choose cubic metrics. I add a new calculated column to the Stock table that calculates the Stock in Cubic metrics by getting the ratio from the measurements table using LookupValue and divide that by the stock number:

StockInCubicMetrics :=

DIVIDE ( [Stock],

LOOKUPVALUE ( Measurement[Ratio],

Measurement[MeasurementId], Stock[MeasurementId]



This gives me the Stock in the single measurement:


Next I create a simple measure that I can use in the PivotTables or other measures:

Sum of StockInCubicMetrics:=SUM([StockInCubicMetrics])

Now finally I want to create a measure that will allow the user to select the measurement he wants to display his stock value in, we will be using a slicer or a filter to select the measurement in the report. First I need to be able to get the ratio of the selected measurement:


Here I used HASONEVALUE to check if one one Measurement is selected. If so return the ration for that measurement if not return 1.

Next I need to create the measure that on the fly calcualtes the stock based on a selection of the measurement:

StockCalculation:=DIVIDE([Sum of StockInCubicMetrics], [RatioValue])

Here is divide the sum of stock in cubic metrics by the ration determined by the selection of the measurement. Again I want to point out that no relationship is used in this model. I use the DAX expression ratiovalue to get the ratio for the measurement I select. I don’t need nor want the behavior that creating a relationship would give me.

Putting it all together:


In this case the Stock calculation measure shows the stock in “cases” as that is selected by the slicer.

You can download the sample workbook here: http://sdrv.ms/JzykOt 


Get data from Facebook with Excel 2013, Power Pivot and Power Query

First of all happy new year!  Lets make it a great Power BI year, one of my resolves is to do more blogging this year.

My wife was talking to me about the need for her to get some information from a Facebook page she is admin for. A project that is getting more and more traction across the us and the globe is the “buy nothing project” (http://buynothingproject.org/) . This project is a hyperlocal Facebook group that enables members to post anything you’d like to give away, lend, or share among neighbors in the same city. Instead of buying it in a store you can get it from a neighbor who doesn’t need to have that specific item any more.

In the project admins group a thread got started on how to get more information on these local group. So I thought lets see if I can get this information from Facebook using Power Query and into Excel. Some information that might be useful is to be able to see if the members of the local group actually live in the same city as the group or what is the percentage of male / female posts or what is the most active hour etc etc.

And I managed to get it working Smile so lets take a look at how this works.

Read more »


Debugging Power View errors with a Profiler trace in Power Pivot in Excel 2013

Another short blog.

Ok you are working with Power View in Excel and all over sudden you get an error message in Power View that doesn’t help at all:

“Power View was not able to complete the action due to a query failure. This can happen if the structure of your data has changed, in which case closing and re-opening the application will resolve the problem.”

and the Error details says:
<Message>Query couldn’t run for dataset ‘DATASET’.</Message>
<ProductName>Power View</ProductName>

It looks you are stuck here as there are no more details, but there is a way to see what is actually going on. You can capture the Power Pivot data model engine trace of everything that is send to the engine and what the engine returns. Go to the Power Pivot tab ,  click the settings button and enable the profiler, a trace will be saved on your machine:



Now make sure you installed SQL Server Profiler, it comes with SQL Server Management Studio, in order for you to open the file.

This opens a very detailed trace file, I ran this one when refreshing a single Power View sheet:


Thanks to this trick the person I was working with was able to figure out that even though his measures worked when testing it they didn’t work in some cases but Power View didn’t give the error message in all cases.

By the way this is also a great way to dive deep into what DAX actions happen under the covers as it also works with Excel Pivot Tables and MDX.

Happy Holidays :)


Project Siena and Power BI

A very brief post today about something really cool. I promise I’ll start blogging more frequent again soon, doing some projects that I will talk about in the new year :).

Today a new Microsoft Project has been released called Project Siena “a Windows 8.1 app that lets you easily create your own Windows app. It’s designed as a quick tool for building Windows 8-style apps without any programming skills.” http://www.theverge.com/2013/12/19/5227140/microsoft-project-siena-windows-apps

The cool thing here is that you can create this Windows 8 app with data pointing to an Excel file that contains an Table created by Power Query or a DAX query. So you can bind any data control to a table in Excel..

Pretty awesome isn’t it :)