Microsoft PowerPivot for Excel 2010 Samples

Now available on MS Download some new PowerPivot samples available for download: test data in workbooks and DAX calculation examples.

Get them here:

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=eac83429-c6e5-48a6-87cf-00a4141e5441

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=1ae63bfb-c303-44e3-ae44-7413d499495d

  • Badman

    Great DAX sample and reference doc. Am struggling to make time intelligence do what I need however.

    ISSUE 1
    As well as having calculated period on columns (ytd, mtd, etc) as per the sample, I’d like to have days nested above calculated period on columns. For example, days 2010-05-01 through 2010-05-07 on columns, with each day drilling into YTD, MTD, etc, with respect to that particular day.

    I can’t make this work. I thought I could simply move the “FullDateLabel” from the report filter to columns, but it doesn’t work.

    Ideally I’d like to have weeks drilling into days, drilling into calculated period….

    ISSUE 2
    I’ve managed to achieve the above requirement of time calculations in relation to days on columns, by creating dedicated measures. For example:

    Value Sales Last Month = calculate(sumx(vw_Facts,[Value_Sales]), DATEADD(vw_Time[Day_Date],-1,MONTH))

    If I then put Day_Date (my date key in my dedicated time dimension table) ON COLUMNS this measure works as expected. If however I attempt to nest Week_ID above Day_Date on columns, the measure returns nothing unless the week is expanded to day. Week_ID is an attribute of Day_date in my time dimension table.

    What am I doing wrong?

    Many thanks in advance.

    kjonge: Fixed the typo and removed other posts

  • Kasper de Jonge

    Hi @Badman ,

    Issue 1: What do you mean with doesn’t work? I have build a sample workbook where it works perfectly. The only thing that happens is that you get a value for every day inside your date table. This is by design and you can work around this. So please supply me with a more detailed problem.

    Issue 2: I would make the measure as following:
    Value Sales Last Month = calculate(sum(vw_Facts[Value_Sales]), DATEADD(vw_Time[Day_Date],-1,MONTH))
    Then try again.

    Kasper

  • Badman

    Hi Kasper,

    Many thanks for you reply.

    I will take another look and get back to you.

    Cheers