PowerPivot time intelligent functions revisited: why use ALL() and how to work around it

UPDATE: In the RTM version of PowerPivot the using of all for Time intel function isn’t necessary any more:  check out the PowerPivot Time intelligent functions golden rules

Last week I got a most excellent question from Sasha at my about page. Using my timeintelligent function screencast Sasha created a workbook using  YTD  where he used one table with facts and dates in it. The problem he had was when created his time intelligent function he wasn’t able to use data from other columns than the date columns. In this blog post i´ll try to describe how the time intelligent functions work, what pitfalls are and how to solve them.

To be able to use a time intelligent function in DAX you use the Calculate function to group measures by a filter. With a time intelligent function you want to filter your values over a period of time (like YTD or previous month).  Most of the time you want the use these functions inside a pivottable where you use dates on the x or y-axis, the values inside the pivottable would show values per the current period context. This would logically result in the following DAX formula:

Dates YTD = CALCULATE(sum(Tablix1[nroforders]),DATESYTD(Tablix1[Date]))

This writes out to: you want the sum of Tablix1[nroforders] from the first Tablix1[Date] value of the year to the Tablix1[Date] belonging to the Tablix1[nroforders] in pivottable context. The YTD of the Tablix1[nroforders] from march 2009 would mean we need to take the sum of Tablix1[nroforders] from all rows from the start of year to march 2009, in the image below you can see a sample of the values that will be summed:

But when we add the formula to the measures and use it in a pivottable we see something strange:

As you can see the nroforders and the YTD formula result in the same values … this is not what we expected. The reason is because the time intelligent function requires an additional parameter, in a blog post at PowerPivotpro.com the PowerPivot product team gave the following answer to my question to why the result is not as expected:

When using Time intelligence functions like LASTDATE or DATESMTD, it is necessary to add another argument which is ALL (TimeTable). Without this only the selected dates are considered, and you can’t find the last month unless you are in the last month.

As you can see in the screenshot this is indeed what happens, the function only uses only the current date context in the sum.

Ok so we need to use ALL to get the results we want, this will result in the following syntax:

DatesYTD w All =CALCULATE(sum(Tablix1[nroforders]),DATESYTD(Tablix1[Date]),all(Tablix1))

This is indeed the result we expected, but having to use the ALL() function has a huge downside.  The ALL() function according to BOL:

Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table.

This means when you use a filter or slicer this is being ignored by the time intelligent function that uses ALL(). As you can see below the results of the YTD are the same as unsliced:

There are two methods we can use to work around this problem:

  1. When you know what slicers and filters you want to work with you can use an ALL() alternative: ALLEXCEPT(). With ALLEXCEPT you can pass through “a list of columns for which context filters must be preserved”.
    In our case we would like to be able to slice on country. This would look like:
    DatesYTD w AllExcept =CALCULATE(sum(Tablix1[nroforders]),DATESYTD(Tablix1[Date]),allexcept(Tablix1,Tablix1[country]))
    I would say use this option if you already know what you want to slice on and don’t have much time to solve it properly.
  2. The proper and most flexible is option number 2. To be able to slice / filter on all the columns you can think of you should create a separate time table. This isn’t very user friendly and your end users will have a hard time grasping this. A few options i can think of to create this time table:
    1. Import the fact table with distinct on date columns (i hope your table isn’t too big ..)
    2. use excel to copy the date rows, remove duplicates and create a linked table (new dates won’t be added)
    3. Supply your users with a default time table in SharePoint/SSRS, they can import this by using the data feed option, use your DWH datetime table as source.

you should create a relationship on the datetime column between the fact table and the the imported time table (make sure your datetime columns have identical granularity, like year, month, day, otherwise the join wont find results). With this relationship in place you now are able to use ALL over the datetime table. When you use ALL() over the separate time table it no longer ignores filters / slicers over your  fact table.
The function would look like:
DatesYTD w All TT =CALCULATE(sum(Tablix1[nroforders]),DATESYTD(DateTable[Date]),all(DateTable))
You now are able to filter or slice all the columns from the fact table you want. In the RTM version of PowerPivot the ALL() in the DAX function is no longer required, the separate time table unfortunately still is.

The final workbook now looks like:

My conclusion is that Time intelligent functions still are a very powerful feature but i am really disappointed on its user-friendliness, while these workarounds are easy for IT/BI personnel to grasp and implement, end users will have a harder time implementing this. Maybe MS can implement a “add date time table” button in PowerPivot to automatically create a date time table to our PowerPivot tables to make it a little easier for end users to implement time intelligent functions.

14 Responses to PowerPivot time intelligent functions revisited: why use ALL() and how to work around it

  1.  

    Uggg. I swear a time-lord would have fits over these things. I am not sure if I understand the work around properly (or if I can even get it to work). My sales (aka facttable) has the date field in it and I have multiple entries for the same date. It does have a unique – record ID field.
    I made a copy of the Sales table, stripped out most of the extra columns and left the RecordID and Sales Date field. I think linked Sales with this called DimSales on the RecordID field.

    Q1. Despite the new date table, the ALL() still removed all filters from preset pivottables. Do I need to have it linked by the Date fields in order for this to work?
    Q2. As I can not have distinct dates on my sales table, will putting distinct dates on my DimSales table omit sales entries as I have many sales per date?
    Q3. You state that with the RTM and future roll-outs, the necessity of the ALL() for time measures won’t be needed, but you state that a separate time table is still required. (a) Why? (b) How do you incorporate the time table in the measures if the ALL command won’t be necessary. (c) If you still need the time table, then what’s the point of removing ALL() as a necessary parameter?

    Sorry – you have answers (and a lovely Danish BI title now I believe – congrats!) that I need. Thanks.

  2. @johncon
    Hi John,

    I have made a new blog post on creating a separate related time table and how to use this in RTM and CTP:
    http://www.powerpivotblog.nl/create-a-separate-related-time-table-for-time-intelligent-functions-in-powerpivot

    You were almost there as i can see from your questions, I hope my new post has answered your questions.

    For Q3 the all() indeed isn’t needed when you use the date field from your related time table as the date column to be calculated upon in your time intelligent function (as you can see in the example of my new blog post).

    I hope this answers your questions, if not don’t hesitate to ask!

    Kasper
    (ps i’m Dutch :) )

     
  3.  

    I’ve starting playing around with the Parallelperiod in DAX. I’ve been using MDX for some years now so I am used to the Paralleperiod function but I face a lot of strange behaviours comparing to what I am used to :) First of all I am not able to use it unless i do the “ALL” thing. Books online suggests the following : =CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PARALLELPERIOD(DateTime[DateKey],-1,year)) but that doesn’t work at all. It needs the ALL. But this will ignore the filters. Using ALLEXCEPT will make this work. But how can I make it work when I want to use Quarters in my rows as well ? Using the ALLExcept ? Great Blog BTW :)

  4.  

    Actually it will work with the “ALLExcept” but it’s kind off “Not that intuitive” :) But I am kind of worried that the “Books online” isn’t correct. Can you confirm that..

    (ps I’m danish :)

  5. Hi @Bobby Henningsen ,

    The two most important things to do when you want to work with time intel functions is
    - Create a separate related time table (dimension)
    - Use a date column to relate to the separate related time table

    Check out this blog post as well:
    http://www.powerpivotblog.nl/powerpivot-time-intelligent-functions-golden-rules

    I think this will help you get the time intel functions to work as expected, the all function shouldn’t be necessary anymore.

     
  6.  

    Spot on. I of course missed the import thing about relationsship with a date column. Which of course makes things a little bit harder since i never use date as key in my time dimension :) Thanks..

  7. Hi @Bobby Henningsen ,
    I think we all never use a datetime as the key. There are several ways to work around this, you can create one using =date() if you have year, month, day available in you fact table.

     
  8.  

    Hi Kasper,
    Thanks for your post. I was facing the same issue for some days and your solution has partially resolved it. Let me elaborate on what is still pending.

    Suppose in the above scenario I have one more attribute on the rows. This attribute changes across months. ie for April it is XYZ and for March it was ABC, then when using this calculated measure I only get data for ABC and not for XYZ.

    I am not sure if this is expected but what if such a result is required.

  9. Hi @Sachin Thomas ,

    Please use the mark as date table function: http://www.powerpivotblog.nl/powerpivot-ctp3-what-is-new-for-time-intelligence-functions

    That should solve it.

     
  10.  

    @Kasper de Jonge
    Already done that.
    Apart from that I have kept the datekey as a datetime column, created relationship between the fact and time dimension. Also the date dimension has continuous dates.

  11. @Sashin,

    Can you please give me an example from the output table? I think I understand the issue but would like to make sure.

    Thanks,
    Kasper

     
  12.  

    Hello Kasper,

    I have been working on a KPI where I show number of visits our sales reps are having each month. I show the KPI each month and I would like to be able to show the number of visits from the previous month.

    I have tried using this formular:
    Previous Month:=CALCULATE(COUNT(appointment[Date]);PARALLELPERIOD(Table5[DateTable];-1;month);ALL(Table5))

    As you can see, I am using a dedicated DateTable – BUT… the result is exactly the same in my “This month” and “Previous Month”

    I would really appreciate it if you can find the time to help me :-)

  13.  

    Here is the formular:

    Previous Month:=CALCULATE(COUNT(appointment[Date]);
    PARALLELPERIOD(Table5[DateTable];
    -1;month);ALL(Table5))

  14.  

    Thanks for your post, Kasper! It helped me a lot!!!

leave your comment


two × = 10