Posts Tagged dax

PowerPivot DAX Session Notes 2 – Previous Row

Those that are familiar with PowerPivot and DAX, will be aware that DAX provides the ability to reference columns.
However, it does not provide support for Cell based references (such as A1, E23).
 
This is normally no big issue, until I hit a small design issue this week… I had the requirement to refer to a previous row of data within the PowerPivot model.
And yes, it is possible, using the EARLIER DAX function.
 
Let me talk through the data quickly… I received some stock futures trading data, where each trade had two rows. One row would be the opening position at a point in time, and when the future actually closed, a second row would come through with the closing position. The profit made would be the difference between the opening position and the closing position.
 
Something like the following table…
 
 
The basic process to solve this problem was to do the following:
1. for every row, determine the Previous Date that applies to this particular Client Code.
2. Once the Date is established, determine the Value at that point in time.
3. Profit is then simply Current Value – Previous Value for CLOSE rows.
 
1. Using the following DAX statement, I could calculate the [DatePrevious] column

=CALCULATE(MAX(Table1[Date]), (FILTER(Table1, EARLIER(Table1[Code])=Table1[Code] && EARLIER(Table1[Date])>Table1[Date])))

Note: I’m still coming to grips with the EARLIER function, seems to work semantically the same as an INNER join… will hopefully post a better explanation when I understand some more…

2. Using similar thinking, I could then join back to [Table1] and return the Value at the point in time established in the previous step.

(I would love any feedback if it’s possible to merge this with the previous step, may be more efficient)

=CALCULATE(SUM(Table1[Value]), (FILTER(Table1, EARLIER(Table1[Code])=Table1[Code] && EARLIER(Table1[DatePrevious])=Table1[Date])))

3. Finally, Profit is then just the difference between the current value and the previous value for the CLOSE rows.

=if(Table1[Position]="CLOSE", Table1[Value]-Table1[ValuePrevious], BLANK())

Results in the table below.

 
Advertisements

,

7 Comments

PowerPivot DAX Session Notes 1 – Dates

Part of my preperation for TechEd Africa this year has required me to really get into PowerPivot and DAX, and I thought I’d share some of the lessons learnt along the way…
 
Dates
 
So, in standard Analysis Services models, the concept of a Date dimension is quite accepted… and the world of PowerPivot is not much different.
Part of creating this type of Dimension / Lookup table involves adding attributes like ‘Month’, ‘Year’, ‘Day of Week’ to a given date. Given the date expressions in Excel, it is quite straight forward to add these attributes using these standard Excel functions.
 
One that I didn’t find out the box though was a MonthName(<date>) function.
So, approaches to getting this right are:
1. Using a massive switch / case statement; yuck 🙂
2. Using a lookup table of sorts to map MonthNumber to MonthName; less yuck.
3. Use Excel date formatting features to achieve the same result… (and yes, I only learnt about this this morning, always more to learn)
 – =TEXT([@Date],”mmmm”)
 
Another common hinderance is the sort order of months.
By default sorting is alphabetical, meaning April suddenly appears before February. (and oddly enough this does confuse end users!)
 
 
Again, the more SQL based approaches would involve injecting some kind of number into the month name and sorting accordingly. (But 02-February is also not so user friendly on reports!)
Fortunately, Standard Excel sorting functions understand Months. So, if you sort the months A-Z, Excel will sort correctly.
 
 

, ,

Leave a comment

SQL PASS: DAX, the language of PowerPivot

PowerPivot, the topic that has been getting all the big attention here at PASS, has introduced ‘yet another’ expression language to define calculations. So, introducing DAX, Data Analysis eXpressions.

The idea behind this language is to provide OLAP type query functionality to the Excel end user. So the basic syntax looks and feels exactly like standard Excel functions. A set of Excel functions have been implemented (80 in total), with additional functions from the Vertipaq engine to allow OLAP functionality.

Ed Note: The additional functions in DAX actually come directly from the VertiPaq engine. Meaning that it is not a wrapper for MDX, it is actually the language used to define and execute the calculation within the engine.

DAX can be used in two places

  • Calculated Columns
    • Similar to creating measures in fact tables, or in the DSV in SSAS. Basically used for line-by-line calculations. These values are calculated and stored at design time, and not recalculated at execution time.
  • Measures
    • Similar to Calculations in SSAS. Calculated at run time, in the context of the current pivot table or query. Best place to define ratios or custom aggregations that don’t make sense in a line-by-line method.

IMHO, the single most important aspect of this language is the underlying relationships defined within the PowerPivot model. As most calculations require some form of navigation between tables, whether it be date calculations or aggregations over groups.

The basic groups of functions that are supported include:

  • Excel Functions
  • Relationships
  • Aggregation
  • Table Functions
  • Time Intelligence

The Time Intelligence ones are by far the most fun ones for me. it is possible to define MDX-type calculations including Year on Year growth, Period to Date and Parallel Period navigation. A bit of a different model though, as the “time dimension” is assumed based on date fields. Only limitation is that it assumes calendar time.

e.g. YOY Growth looks like

Fact([Sales]) – Fact([Sales])(DateAdd(Time[Date], -1, YEAR)

Overall impression is very positive. Full descriptions available at http://blogs.msdn.com/powerpivot/archive/2009/10/01/introduction-to-data-analysis-expressions-dax-in-gemini.aspx

,

Leave a comment