Microsoft PowerPivot for Excel 2010 Samples

3 Replies to “Microsoft PowerPivot for Excel 2010 Samples”

1. 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

   Reply

2. 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

   Reply

3. Hi Kasper,

   Many thanks for you reply.

   I will take another look and get back to you.

   Cheers

   Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.