Now available on MS Download some new PowerPivot samples available for download: test data in workbooks and DAX calculation examples.
Get them here:
3 Replies to “Microsoft PowerPivot for Excel 2010 Samples”
Great DAX sample and reference doc. Am struggling to make time intelligence do what I need however.
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….
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
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.
Many thanks for you reply.
I will take another look and get back to you.