PowerPivot Time intelligent function and other DAX example

I was working on a presentation on PowerPivot and decided to share these two quick sample with you, just because i still am amazed by how simple working with DAX is. I am using data from the Contoso database.

First example shows you a time intelligent function sample where i want to see in a chart the daysinstock of this year compared with the days in stock of last year. We start by adding the sum of daysinstock to my chart with Calendaryear and the monthnumber (which is a calculated column in my table monthnumber = MONTH(DimDate[Datekey]) )

Which gives this chart:

I want to have stacked charts to show me the difference between the years and months, to get the value of the previous year is create a function:

DaysinStock last year =CALCULATE(sum(FactInventory[DaysInStock]), DATEADD(DimDate[Datekey], -1, year))

This gives me this chart:

Ofcourse we have information in 2010 as well because 2010 has values of 2009 in the previous year measure. I want to suppress values in the previous year measure when the current year doesn’t have values. We can do this by using an IF inside the measure:

DaysinStock last year =if(ISBLANK(sum(FactInventory[DaysInStock])),blank(), CALCULATE(sum(FactInventory[DaysInStock]), DATEADD(DimDate[Datekey], -1, year)))

This measure will check is the sum(FactInventory[DaysInStock]) returns a blank value in the current year, is that is true it will return a blank value of last year as well. If it the current year does have a value it will return the value of last year. This will result in this chart:

You can see how easy you can add exceptions to you measure with DAX, whatever your customers want.

For my next example I want a chart where i want to see the Avg days in stock for each month (sliceable by Year), we again start by adding values from my data to a chart, we changed the default summerize by Sum to Average in the PowerPivot field list and this gives us the following chart:

Now i want to compare these values by the average value of all the months of all the selected years on my slicer. We can do this by using the ALL function. The formula will look like:

Yearly avg days in stock = CALCULATE(AVERAGE(FactInventory[DaysInStock]), all(DimDate[MonthNumber]))

This will do an average over FactInventory[DaysInStock] using all the months in the data selection. So when you select multiple years it will get me the months of all those year, when we select a single year only the months of that year will be returned. So the calculate will calculate an average over all the months instead of only one month.

When we select all years this wil look like :

When we select a single year it will give the average over all the months in the selected year:

Again shows you the power and simplicity of DAX.