Now available on MS Download some new PowerPivot samples available for download: test data in workbooks and DAX calculation examples.
Get them here:
All about Power BI, Modelling, DAX and Microsoft BI
Now available on MS Download some new PowerPivot samples available for download: test data in workbooks and DAX calculation examples.
Get them here:
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
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
Hi Kasper,
Many thanks for you reply.
I will take another look and get back to you.
Cheers