Time Intelligence in DAX: Last n days



How to formulate Last n days in DAX?


It can be achieved using the combination of CALCULATE & DATESINPERIOD function.

Formula to sum up values for last 10 days

[code type=”sql”]

Note the use of a calendar table in the DATESINPERIOD. It is pretty common to have a “date” dimension & I’ve used it the DateKey from the dimension as the “date” column which is required by the DATESINPERIOD function.

You can use the above example as a starting point now.

SharePoint PowerPivot Site: How to extend document library to connect to Tabular Model for some Power View magic!


So you are SharePoint site admin and your task is to extend Document Library to connect to SSAS Tabular Model so that your Power View reports can connect to Tabular models. we’ll see that in this blog-post.

Note: Creating a SharePoint Business Intelligence Dev Environment is a different thing, please refer: Bird’s Eye view of SharePoint BI Dev Environment Setup process and official reference http://msdn.microsoft.com/en-us/library/hh231687%28v=sql.110%29.aspx

In this Blog Post, I am assuming you are through step 1- 8 and we’ll see a Tutorial on Step 9: “Extend the Document Libraries to include BISM connections.” so that we can create Power View reports on Tabular Models.

Here are the steps:

1) Open SharePoint PowerPivot site

2) PowerPivot site > Documents > Do you see New Document disabled? Well, we are going to fix that!

Power Pivot Site New Document Disabled

3) Switch to PowerPivot Gallery > Library > Click on Library settings:

Power Pivot Site Gallery Library Settings

4) Click on advanced settings

Power Pivot Document library advanced settings

5) Here change the setting of “Allow Management of Content Types?” to YES

And Click on OK

6) Back on Document Library Settings Page, Under Content Types, click on “Add from existing site content types”

Power Pivot document library setting BISM

7) On “Add Content Types” Page > from Available Site Content Types – Add “BI Semantic Model Connection” to content types to add. and click on OK.

BI semantic model connection content type

8) Now,

Go to PowerPivot Gallery > Documents > New Document.

Can you see BI Semantic Model connection? Yes? Awesome!

create a new BI semantic model connection

9) Now let’s test it.

Note: I am on my Dev Machine so I am going to test it by just connecting a Tabular Model that’s running on same machine.

BISM connecting to a Tabular Model for Power View

Click on OK.

10) can you see the connection? Yes? Click on Power View icon.

can you see the BISM connection

11)Can you see the view? yes? Go ahead! Create Awesome Power View reports!

sample power view report

That’s about it for this post.