zondag 21 februari 2016



I'm a fan of Lean Six Sigma. The pragmatic toolbox of Lean and Six Sigma has all kinds of tools and helpful aids to improve processes. In the past I have learned tools like Ishikawa (fishbone diagram), 5 times why, and Value Stream Mapping.

The helpful tools of LSS can help your project or department to gain more efficiency, to be more effective and to have more fun. With the tooling you can iteratively improve your way of work. And it doesn't need much time to do it. Just ask your team members every week their biggest irritation and improve this in a PDCA or DMAIC cyclus. Another great tool is to improve the visibility and communication in your team with a Kanban board.

Many of the things you learn, you can adopt this in your daily work and personal life. The way of thinking helps you improve processes or your daily life. Looking at my desk I could adopt the 5S method for sure ;-)


Lean started when processes became more common, for instance when Henry Ford started to build T - Fords. As soon as a process involved you can improve the process by making (small) adjustments to the process. Off course, Toyota is the example of Lean implementation in a automobile factory, called Toyota Production System (TPS), but it were the Americans that helped the Japanese build up the industry after World War II. Deming was the person that showed that you can increase quality with lower costs, by reducing wastes and more. The PDCA cyclus embodies the continuous improvement. See here for information.

Lean basics

There are 5 dimensions to support improvement in Lean : customer, process, organization, performance and behavior & attitude. There is nothing to add value when there is no customer. Customers wants products and services to add value to his or her work or life. There is also a process that you want to improve and bring it under control with people, materials and the talents of people. It's also needed to shape the organisation in order to maximize the value. And, how do we need to measure this? There are steps that improves the overall performance and there steps that brings down performance and you want to know this.

The goal is to optimize adding value (value add) to the activities instead of non value tasks (non-value add). Examples of value adding work are building a data warehouse or an analysis. Examples of non value adding activities are doing more than needed or rework. There are also activities that are needed but do not add value to activities, but it needs to be done (necessary non-value add), for instance testing.

Lean is a continuous improvement approach and focuses on design and improvement of the process. ITIL also talks about continuous improvement but does not give you the toolbox how to improve the process. Lean will you give you tooling how to execute continuous improvement.


This blogpost is a small blogpost about Lean. Lean is in the same category as scrum or agility and I'm convinced they can work together in synergy.



zondag 14 februari 2016

DAX : Building a monthly salesreport with estimation in PowerPivot (Part III)


This is the third blog post about the Daily Sales dashboard that I'm currently building in PowerPivot and in PowerView (Excel 2013). I am continuing to learn more and more about DAX expressions. Everytime I go a step further in the development of the PowerPivot workbooks. I see new opportunities and using better DAX expressions. This blogpost describes an evolution in the development of a daily sales report rather than a subsequent blogposts of other blogposts I have written about this subject, so far:

The case

The purpose of the workbook is analyzing the daily sales in a month. I want to see the sales in the current month but also in the previous month. I have the following requirements :
  • Report the sales in the current month in days.
  • Report the sales of the previous month in days.
  • Report the net revenue of the month so far (Month to Date).
  • The projected net revenue for the rest of the month based on the Month to Date.
  • The projected net revenue in a month (based on the MTD) vs the Same Month last Year.
  • One tab for the sales per day.
  • One tab for the sales per Brand (a collection of products).

There are some considerations that makes it interesting:
  • Products sold in the current month or previous month may not have been sold in the same month previous year.
  • Products that have been sold in the past may not sell in the current month or sold in the previous month.
  • Weekends and holidays should be excluded from the calculations. For example, if the day is a holiday it is not a working day (this is has impact on the projected sales).

There are some limitations:
  • The clients are 32bits machines. Therefore we can't store all the data in the PowerPivots because of memory limitations. For this reason I've created a view for the current month, previous month, same month last year and the previous month. So I've included 4 periods in my data to limit the amount of the data.

The Data

For this blogpost, I have created some test data for the 4 periods that I want to simulate. I have created three tables, two dimensions and one facttable.


For this blogpost, I have created an Excel spreadsheet that contains the most relevant data. To make life easier I included an extra column WorkingDay that is a walking number that doesn't add up when it's a weekend or a holiday. 


The Product dimension is a simple product table with Productdescriptions and brandnames.


The FactSales table is the fact table with the references to the dimensions and the measures in this case SalesQTY and UnitPrice.

The datamodel

In this mock up there are three tables in the PowerPivot workbook: DimProduct, DimCalendar and FactSales. The data model is as follows:

Month Selector

The next step is introducing a month selector in the workbook. As I've mentioned in my previous blogpost, the monthselector selects the current month and a previous month based on the actual data in the Fact table.

 DimCalendar[MonthNumber] = MONTH(LASTDATE(ALL(FactSales[CalendarDate]))) && 
 DimCalendar[Year] = YEAR(LASTDATE(ALL(FactSales[CalendarDate]))), 
 "Current Month",
    DimCalendar[MonthNumber] = MONTH(EDATE(LASTDATE(ALL(FactSales[CalendarDate])), -1)) && 
    DimCalendar[Year] = YEAR(EDATE(LASTDATE(ALL(FactSales[CalendarDate])),-1)), 
    "Previous Month"

As you may see in the screenshot, the monthselector not only selects current month and previous month but also the other months (that I've included for same month last Year calculation) I want to exclude this from my selection.

The logic I have used in my month selector should also be included in the SumNetRevenue DAX Expression. To remove an error message I've also added the MAX() function in the DAX expression.

 MAX(DimCalendar[MonthNumber]) = MONTH(LASTDATE(ALL(FactSales[CalendarDate]))) &&  
 MAX(DimCalendar[Year]) = YEAR(LASTDATE(ALL(FactSales[CalendarDate]))), 
 MAX(DimCalendar[MonthNumber]) = MONTH(EDATE(LASTDATE(ALL(FactSales[CalendarDate])), -1)) &&  MAX(DimCalendar[Year]) = YEAR(EDATE(LASTDATE(ALL(FactSales[CalendarDate])),-1))
                SUMX(FactSales, FactSales[SalesQuantity] * FactSales[Unitprice])


The next step is adding a Month To Date Net Revenue measure for the revenue in the current month and in the previous month.

MTDNetRevenue:=CALCULATE([SalesAmount], DATESMTD(DimCalendar[CalendarDate]))

This results in the following pivot:

As you may see in this pivot is that the MTD continue until the end of month and that is not really desirable. Therefore we need to adjust the DAX expression a bit.

                  CALCULATE([SalesAmount], DATESMTD(DimCalendar[CalendarDate]))

And now the empty cells for net revenue are gone.

Let's see what happens for the previous month.

Projected Net Revenue

The next step is to calculate the projected net revenue for the current month. This is a calculation of an estimation of the sales based on the current MTD netrevenue or the rest of the month. This way it's possible to determine whether the sales are on track on or not. The calculation for the projected netrevenue is:

MTDNetrevenue * SumOfworkingDaysPerMonth/CurrentWorkdayoftheMonth

Let's build this in a DAX Expression:

ProjNetrevenue:=DIVIDE([MTDNetRevenue] * [SumOfWorkingDaysPerMonth], 


This is for the current month:

And the screenshot below is for the previous month. You can compare the estimation and the actual values and validate the predictions.

Let's check a value and see if the calculation is right. We take the values of 19/1/2016. This is the twelfth working day in the month and the total number of working days in the month is 20.

 ProjectedNetrevenue = 161.5*20/12 = 269,17

And this is correct.

Now we want to compare this with the complete month but in the previous year for the current month and the previous month. Now, I've written the following DAX Expression to solve this:

          FILTER(ALL(DimCalendar), DimCalendar[Year] = MAX(DimCalendar[Year]) -1), 
          FILTER(ALL(DimCalendar), DimCalendar[MonthNumber] = MAX(DimCalendar[MonthNumber]) )

Or use an improved version (with PARALLELPERIOD):

                       PARALLELPERIOD(DimCalendar[CalendarDate], -12, month) 

This results in the following pivot (for the current month)

And this is for the previous month

Now we can calculate the %projectedvsSMPYNetrevenue

%ProjectedvsSMPYNetRevenue:=DIVIDE([ProjNetrevenue] - [SMPYNetRevenue], [SMPYNetRevenue])

And this results in the following pivots (for the current month):

For the previous month:

NetRevenue by Brand

In the former paragraphs we discussed the net revenue split by day. In this paragraph we add an extra tab to the worksheet and build a Daily sales report for the brands (Dimproduct). For this reason I have adjusted the Same Month Previous Year Net Revenue calculation a bit (SMPYNetRevenue)

       ALLEXCEPT(DimCalendar, DimCalendar[CalendarDate], DimCalendar[Monthselector]),
       FILTER(ALL(DimCalendar), DimCalendar[Year] = MAX(DimCalendar[Year]) -1), 
       FILTER(ALL(DimCalendar), DimCalendar[MonthNumber] = MAX(DimCalendar[MonthNumber]) )

Or use the version with PARALLELPERIOD :

       ALLEXCEPT(DimCalendar, DimCalendar[CalendarDate], DimCalendar[Monthselector]),
       PARALLELPERIOD(DimCalendar[CalendarDate], -12, month) 

And in the screenshot below the Same month Previous Year calculation .

Projected net revenue

Yet another challenge is that the projected Net Revenue depends on the selection of the Calendardate slicer. If a date is selected, the projected net revenue is calculated based on the current working day. If the analysis is done in the pivot on a daily basis (row context) then it's no problem because the projected net revenue expression can calculate the projected net revenue of the month with the rowcontext. In case of the net revenue per brand there is not a date selected. You have to select a date and the projectednetrevenue is properly calculated. For this reason I adjusted the projected net revenue DAX expression to :

ProjNetrevenue:=DIVIDE([MTDNetRevenue] * [SumOfWorkingDaysPerMonth],

Remove the (blank) from the slicer

The last problem I would like to discuss is the (blank) in the Slicer of the monthselector. Because I need the data of the periods for the calculations it's not possible to remove this, but I don't want the (blank) in the selector because it confuses users. 

Too bad, there seems no real solution for this. The solution that I read from the PowerPivotPro forum was the following:
  1. It's only possible for vertical slicers (set it to vertical).
  2. Reorder the slicer with the Descending.
  3. Select "Show item with no data last"
  4. Adjust the window such that you can only see Current and previous month

And this is the result:

Not really happy with this solution but it seems that there is no better solution. Suggestions are welcome.


DAX is interesting for analyzing and building dashboards and great reports in Excel PowerBI (v1 en Desktop). Sometimes I lack the possibility to do more customization of the components in Excel.



woensdag 13 januari 2016

DAX : Building a monthly salesreport with estimation in PowerPivot (Part II)


I'm not happy with the solution presented in my former post about Daily sales report because the current month is determined with the NOW() function. Rob Collie would say that the every time you look at a PowerPivot workbook you think: "that can be improved". The NOW() function calculates the current date like the GETDATE() function in SQL. This solution has some flaws, because when a user comes back months later and he or she wants to use this workbook again it won't work because the data is from months earlier. So, we have to think about a more robust solution.

When searching the web I was looking at the blogpost of Kasper de Jonge: "Use PowerPivot DAX to automatically report on the last month that has data" and he exactly covers this problem. In this blogpost I'll use this as a base for my solution.


First I decided to add a monthselector slicer to my workbook. I changed the following code (the one with the NOW() function). I created this monthselector as calculated column in the Calendar dimension. This is the initial monthselector Dax expression.

  MONTH(Calendar[CalendarDate]) = MONTH(NOW()) && 
  YEAR(Calendar[CalendarDate]) = YEAR(NOW()), 
  "Current Month",
   MONTH(Calendar[CalendarDate]) = MONTH(EDATE(NOW(),-1)) && 
   YEAR(Calendar[CalendarDate]) = YEAR(EDATE(NOW(),-1)), 
   "Previous Month"

This is the final DAX expression:

 Calendar[MonthNumber] = MONTH(LASTDATE(ALL(Sales[CalendarDate]))) && 
 Calendar[Year] = YEAR(LASTDATE(ALL(Sales[CalendarDate]))), 
 "Current Month",
    Calendar[MonthNumber] = MONTH(EDATE(LASTDATE(ALL(Sales[CalendarDate])), -1)) && 
    Calendar[Year] = YEAR(EDATE(LASTDATE(ALL(Sales[CalendarDate])),-1)), 
    "Previous Month"

I had to add a 'trick' to get it working. I added a RELATED() function in the Facttable to get the CalendarDate column. If I can remove this in the future I'll let you know..

And this is the result with the slicer in the PowerPivot.

Other improvements

Now let's take a look at the estimation DAX expression. As you may have read in my former blogpost, the estimation is the estimated sales for the rest of the month. This is now based on the Month to Date NetRevenue. This is also changed.

This is the DAX expression to determine the estimated sales amounts. As you can see the logic to determine the workingday - based on the a normal day in the calendar dimension- is removed. This automatically done by the PowerPivot engine. So, this is simplification with my initial solution.

                           DIVIDE([SalesAmountMTD] * [SUMWorkingDaysInMonth], 

The expression of the Month to Date Sales Amount :

                  Calculate([SalesAmount], DATESMTD(Calendar[CalendarDate])))   

This is the DAX expression of the SumWorkingDaysInMonth:

SUMWorkingDaysInMonth:=CALCULATE([NumOfWorkingDays], ALL(Calendar[CalendarDate]))


NumOfWorkingDays:=CALCULATE([NumOfDays], Calendar[WorkingDay] = "Y")

This is the result for the previous month

And this is the result for the current month:


Everytime you work with DAX, you learn better ways with DAX to build your solutions. I'm working now a while with DAX expressions and every time you learn new tricks.

In this blogpost I've used a slicer based on the data in the fact and I've simplified my solution of the part I blogpost.


zondag 10 januari 2016

DAX: The very long message "The semantic error or perhaps this error or this error.."

One day, I was working with a DAX expression in PowerPivot (Excel 2013) and I got the following error:

"Semantic error: The value for column 'Date' in Table 'dimCalendar' cannot be determined in the current context. Check that all columns in referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation -- such as sum, average, or count-- on that column. The column does not have a single value, it has many values, one for each row in the table, and now row has been specified."

Okay.. I prefer shorter messages normally and this error message popups in a tooltip and this tooltip disappears after a couple seconds. You have to move the cursor back and again and then you have a couple of seconds to read the tooltip again, before it disappears again. Well, I have written it all out in this blogpost and so you can read it at ease with a cup of coffee.

This was this the DAX expression that generated the error:

SumNetRevenue:=IF(MONTH(DimCalendar[Date]) = MONTH(NOW());

Now, the second part of the error is the interesting part and that said that there are different granularities in the expression. The SUM is on filtercontext but, the DimCalendar[Date] is on row level. I solved it with this DAX Expression:

SumNetRevenue:= IF(MONTH(MAX(DimCalendar[Date])) = MONTH(NOW()) ;


Don't use very very long error messages in a tooltip ;-)


dinsdag 15 december 2015

DAX : Building a monthly salesreport with estimation in PowerPivot (Part I)


In this blog post I would like show you step-by-step how to build a report in PowerPivot with DAX expressions. For my current customer I have to build a report that calculates the daily sales and a comparison with a budget. I've gathered the following requirements:
  • Calculate monthly sales to determine how much of this product has been sold per company, day and month.
  • Extrapolate monthly sales (estimation) and compare this with the budget.

The following restrictions are relevant here:
  • The working days must be taken into consideration.
  • Holidays must be taken in consideration.
  • The targets are monthly based.

This blog post is based on the paragraph entitled "Working days" from the book "The definitive guide to DAX" by Marco Russo and Alberto Ferrari.

The datamodel

A proper data model is designed with a sales fact table and a calendar dimension table. Rob Collie refers to these as data lookup tables and  data tables, but I prefer dimension and fact tables.

Calendar dimension 

For designing and filling the calendar dimension I would like to use a previous blogpost of mine in which I discuss building a calendar dimension in Excel. For this blogpost, I haveextended this example with the following fields:
  • DayInWeek : = WEEKDAY(B2)
  • WorkingDay : = IF(OR(WEEKDAY(B2)=1, WEEKDAY(B2)= 7), "N", "Y")
  • Holiday : Manually entered values Y for a holiday like Easter and N if not.
  • MonthNumber : = MONTH(1&LEFT(G10,3))

Below is a snippet of the calendar dimension where I included the holidays Easter and King's day (in the Netherlands).

This example was created with Excel but you can also generate this in SQL Server with a stored procedure for instance. This I would normally prefer.

Sales sample data

For example, I have created some sales data in the workbook. I have gathered some data for two months - March and May - because there are some holidays in these months and therefore it's possible to test the holiday scenario.

In this example, there are products sold on weekends and weekdays.

The PowerPivot data model

The next step is building the PowerPivot datamodel. This can do done by using CTRL-T of the table in Excel. Then give the table a name and add this to the data model. 

Don't forget to mark the calendar table as a date table in order to use time intelligence functions.

Building the measures

Normally, the next step would be to build the explicit measure layer in PowerPivot. In this layer the data source and the rest of the DAX expressions are separated from each other. But, for the sake of this example, I will not do that. I have created the following DAX Measures:


NumOfWorkingDays:=CALCULATE([NumOfDays], Calendar[WorkingDay] = "Y")

NumOfWorkingDaysIncHolidays:=CALCULATE([NumOfWorkingDays], Calendar[Holiday] = "N")

SalesAmount:=SUMX(Sales, Sales[SalesQuantity] * Sales[Unitprice])

DailySales:=DIVIDE([SalesAmount], [NumOfDays])

WorkDailySales:=DIVIDE([SalesAmount], [NumOfWorkingDays])

HoliWorkDailySales:=DIVIDE([SalesAmount], [NumOfWorkingDaysIncHolidays])    

These measures make a distinction between days, workingdays and holidays.

Let's take a look at the pivot table. For sorting the months I added an extra column in the calendar dimension MonthNumber (=MONTH(1&LEFT(G10,3))) and G10 is the Month name. Nice trick.

For instance, April has 30 days, of  which  22 are workingdays. However, when the holidays are subtracted we can see that there are really only 20 days for labor.

Now, let's take a look at the SalesAmount in the pivot table:

I have set the settings of the PivotTable to show the rows even when there is no data for a month. Now you can see that the Grand Total of Daily Sales is much lower than the average for April and May. If we divide 416.68 (the total annual sales) by 365 days we get 1.14, but that is not right. Thus it's better to divide it by something else. We have to neglect the days there was no data in the sales table.

NumOfDays:=IF([SalesAmount] > 0, COUNTROWS(Calendar))

But nothing happens. It only clears the values if you have a larger calendar dimension than I have. Marco Russo and Alberto Ferrari call this a "granularity mismatch" in their book "The Definitive Guide to DAX". The numbers are accurate at the month level but inaccurate at the Grand Total level. The problem is how to determine the right granularity level. This is a business rule and it depends on how you build the pivot table in your report.

We have to use an iterator: SUMX and SUMX iterates over the values and sums them by a certain level of granularity. In my case I would like to use a monthly granularity.

DailySales:=DIVIDE([SalesAmount], SUMX(VALUES(Calendar[MonthName]), [NumOfDays]))

WorkDailySales:=DIVIDE([SalesAmount], SUMX(VALUES(Calendar[MonthName]),[NumOfWorkingDays]))

DIVIDE([SalesAmount], SUMX(VALUES(Calendar[MonthName]),[NumOfWorkingDaysIncHolidays])) 

And this results in the following pivottable:

And I manually calculated the Grand Total of the Sales measures and the numbers are correct.

So where are we now?

In the beginning of this blog post I listed gathered some requirements. Let's see what we have done so far:
  • Monthly sales. How much have we sold of this product or per company.
  • Extrapolate of monthly sales and compare this to the target.
  • Take the working days in consideration.
  • Take the holidays in consideration.
  • The targets are monthly based.
In the next section I would like to give some thoughts on extrapolation of the current month sales, This way we can determine whether the monthly sales targets and actual sales are on target.

Estimated Sales

For this reason, I have entered some extra data to the Sales table until 'today'.

Furthermore, I have added an extra column to the Calendar dimension, WorkingDayInMonth and this is a running number in the Calendar dimension that is raised by 1 of the day is a working day and not a holiday. Below is an example from the month December for which I have added some test data for the WorkingDayInMonth column.

By doing so, I can lookup the current working day that depends on the current day of the month. For instance, it's now December 14th and the current working day is 11. It is now possible to calculate the sales amount that is earned by the company and then calculate the extrapolated sales amount for the working days left in the current month.

I have added some extra measures to the Power Pivot workbook.


WorkingToday:=LOOKUPVALUE(Calendar[WorkingDayInMonth], Calendar[CalendarDate], Calendar[Today])

SalesEstimation:=IF(MONTH(NOW()) = MAX(Calendar[MonthNumber]), 
 DIVIDE(([SalesAmount] * [NumOfWorkingDaysIncHolidays]), [CurrentWorkingToday]),[SalesAmount]) 

The measure 'Today' is used by the other measure 'WorkingToday' for looking up in the Calendar dimension. The WorkingToday is the current working day in the current month. Perhaps you would like to limit this measure by the current month scope, but I will leave that up to you.

The measure SalesForecast calculates the estimated sales in the current month by using the following pseudo-formula:

EstimatedSales = SUM(SalesAmount/WorkingDaysSofar x NumOfWorkingDaysIncHolidays)

And this is the result of the calculation (I renamed SalesForecast to SalesEstimation):

At this point there is only one problem and that is the SalesEstimation on the Grand Total level.

In order to avoid confusion,  I added an extra IF to the measure :

IF(COUNTROWS(VALUES(Calendar[MonthNumber])) = 1, 
  IF(MONTH(NOW()) = MAX(Calendar[MonthNumber]), 
 DIVIDE(([SalesAmount] * [NumOfWorkingDaysIncHolidays]), [CurrentWorkingToday]),[SalesAmount]),

Resulting in


This blog post describes an implementation of a Daily Sales Report for a month with an estimation of sales amount in the current month.