For time intelligence in SSAS Tabular models, it’s important to mark a date table. So, how do you do that?
Here are the steps:
1. After you have the data imported in a table, in the model designer, select the date table
2. Also, make sure that necessary relationships are created between date table and fact tables.
3. Now, on the menu bar, select Table > Date > Mark as Date Table
4. A dialog box should ask you for a unique identifier of the date table, so it could be a date column which has the unique columns for the date table that you imported.
Also, make sure that the unique identifier does not have blank values because the model designer will not allow you to select it as your unique identifier and so you may have to go back to your table properties and put a logic to filter out blank values or make sure that the a row with blank value has a value assigned to it.
After you do that, you’re all set to go! I hope this helps.
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.
DAX (Microsoft’ Data Analysis Expressions Language) does not have a Substring function but I needed something like that for the following problem:
I had domain/username as input and I needed to extract just the username part of the string.
Input format: domain/username
output format needed: username
Input column name: UserID
so here’s the DAX formula I used: RIGHT([UserID],LEN([UserID])-SEARCH(“”,[UserID]))
Note: The Formula is shown for demo purpose only, It may not work directly before making appropriate changes to the formula like making sure the column name is right & If the double quotes show problems, try deleting it & typing them back again.
Note that I combined some of the available DAX text functions to achieve what I was looking for. There might be other way to do this and I would be happy to learn about it too. Meanwhile, I hope if you reading this, this might give a good starting point while researching your DAX problem.
A quick blog post about sharing PowerPivot DAX formula to calculate moving average. Please use this as building block for your scenario:
Here’s the Data:
(usually, the date would in a date table. For the purpose of sharing building block of this formula, I kept it this way to keep it simple)
Formula (Calculated Measure):
[code language=”text”] Moving Average Last 3 days:=CALCULATE(AVERAGE(TableName[Amt]),DATESINPERIOD(TableName[date],PREVIOUSDAY(TableName[date]),-3,day)) [/code]
Results via a Pivot Table:
Note that the formula is a building block and you’ll have to make changes as per your requirement and data model. Feel free to leave a comment if you need some assistance from my side. And also consider using the PowerPivot forum to reach out to community: MSDN Forum – PowerPivot for Excel