Multiple hierarchies and xTDs


Last week I was challenged by a co-worker: *‘create a way that a tabular cube case serve a YTD value for different time hierarchies.’ *Because Iwork at a retailer at this moment, there is a fiscal calendar and a 454 calendar, which of course doesn’t align in the begin and end date.

While I was writing this post, Marco Russo wrote a great post on how to createdifferent xTD values. Please read it: http://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/ (Note tomyself: do not postpone the writing of your posts)

This has one advantage for me: I don’t have to explain the logic behind xTD who are not based on a standard calendar. Lucky me :-)

Continue Reading

Adding #Trendlines to #PowerPivot for #PowerView


Adding trendlines to PowerPivot

It started during a presentation of Cathy Dumas at the latest SQLBits where she compared the different charting capabilities of Excel, Power View, PerformancePoint and Reporting Service. One of those capabilities were trendlines who are missing in V1 of Power View. She suggested to add a trendline as a measure, but didn’t succeed, so she raised a challenge. And here I am taking this challenge to add a trendline to a PowerPivot model to be used in Power View.

Trendline theory

The theory behind a linear trendline is minimizing the least-square of the given set. The Wikipedia page gives a lot of theory and I must admit that the Dutch version has besides the theory

Continue Reading