Problem:
How do you get a Month Name from a date field in Power Pivot?
Solution:
here’s a code snippet that should help:
[code language=”SQL”]
=FORMAT([date],"MMM")
[/code]
This should give you month names (Jan, Feb, …) instead of integers that are returned by the MONTH function.
couple of notes:
1. date field needs to be used to get the month name
2. MMM needs to be in uppercase.
I hope this helps.
Problem:
For time intelligence in SSAS Tabular models, it’s important to mark a date table. So, how do you do that?
Solution:
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.
Problem:
How to formulate Last n days in DAX?
Solution:
It can be achieved using the combination of CALCULATE & DATESINPERIOD function.
Formula to sum up values for last 10 days
[code type=”sql”]
MeasureName:=CALCULATE(SUM([YourColumnName]),DATESINPERIOD(Calendar[DateKey],TODAY(),-10,day))
[/code]
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:
| DATE | AMOUNT |
| 6/1/2013 | 50 |
| 6/2/2013 | 40 |
| 6/3/2013 | 30 |
| 6/4/2013 | 20 |
| 6/5/2013 | 10 |
| 6/6/2013 | 10 |
| 6/7/2013 | 20 |
| 6/8/2013 | 30 |
| 6/9/2013 | 40 |
| 6/10/2013 | 50 |
(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)
[code language=”text”]
Moving Average Last 3 days:=CALCULATE(AVERAGE(TableName[Amt]),DATESINPERIOD(TableName[date],PREVIOUSDAY(TableName[date]),-3,day))
[/code]
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