YTD is easy with “standard” calendars, but the previous year’s equivalent is a non-obvious formula. Back in the Saddle! What’s this, you say?? Yeah, it’s Rob, and I’m here with an actual formula post! Woo woo! Starting the year off…
By Dany Hoter
(Download file here to follow along).
Time intelligence functions are some of the most important functions in DAX.
Being able to compare values between current period and the same period last year is a very common request and one that is a real challenge using native Excel and can easily achieved with DAX.
A typical calculated field might look like:
Sales Last Year:=CALCULATE(
In quite a few cases once you start using such expressions you will see this annoying error message:
The reason is that the time intelligence functions like the one used here SAMEPERIODLASTYEAR require 100% completely consecutive dates in the filter context, with no holes.
Guest Post by Willem van Dijk
I am, like most (if not all) visitors to this website, an addict for information –> data –> truth –> Εμπειρία <-hope that sounds like Greek to you…
My journey into Power Pivot has been one of uphill struggles and downhill thrills. It’s been brief moments of ego feeding, immediately followed by lengthy bouts of meekness. One moment I am Indiana Jones going for his gun in The Raiders of the Lost Ark directly followed by the same scene in The Temple of Doom
Where did my DAX go?
Our “Morning” Website Traffic is Down 21.5% in Jan 2014 vs. Jan 2013, But “Evening”
Traffic is Up by a Similar Amount, and Full-Day Traffic is “Flat” at +0.9%
(Fake Data, But Real Analysis)
Two Different Flavors of “Time”
Usually, when we talk about “time” in Power Pivot, we’re talking about the Calendar/Date flavor: How much have things changed from yesterday to today. What are our Month to Date numbers, and how do those compare against the same period last year? Let’s call this “macro-trending.”
But time of day is also often interesting: what are the trends WITHIN a day? Let’s call this “micro-trending.”
And then, the hybrid of the two: how are our “micro” trends changing over the course of the year, month, quarter, etc?
I don’t think the techniques here are terribly complicated, but they might be a little difficult to conjure up on your own. So, it’s time for a post – and a downloadable workbook!
The Key: Separate the Date and Time Components!
As part of the microsoft.com/learning team we release products throughout the year –courseware, books, exams and eLearning (check out MVA for tons of free courses). We often need to understand how our products perform after launch and how they compare against each other (in the first month since launch, first quarter, first year etc.). For Example: we would compare the various courses we launched around Visual Studio 2010. We may compare Visual Studio 2010 against Visual Studio 2012 courses. We may even compare Visual Studio against SQL Server. Or compare adoption by geography or customer segments.
I can imagine similar need for other businesses, e.g. a car manufacturer who needs to compare performance of various year, make and models.
Power Pivot and Power View can allow us to go from View 1 below, which is inscrutable at best, to View 2 which really helps us understand and differentiate the adoption ramp of various products. In this article, I would explain how you can go from View 1 to View 2 using the car manufacturer example.
View 1: Monthly Sales by Car Model
View 2: Cumulative Sales since Launch, by Car Model
Have You Ever Seen This Cryptic Error? Here’s a Quick Explanation and Fix
So you’ve got a DATEADD measure…
Let’s say you’ve written a nifty measure called [Last Year Sales] that returns the sales value you had one year ago at the same time:
[Last Year Sales] =
CALCULATE([Total Sales, DATEADD(Calendar[Date], –1, Year))
And hey, that works great:
So far so good: [Last Year Sales] Measure is Working Great!
Two Weeks of Refreshes Later… the Report Still Thinks
Nov 15 is What Everyone Wants to See First!
Your Dashboard is Refreshed, But its Slicers are Stuck in “Yesteryear”
This is a trick I’ve been using forever but never blogged about. Enough of you are now using Power Pivot for SharePoint (PP Server) that its time has come. And really, it’s relevant on the desktop too.
On the day you first made this report, you selected the most recent Date (or Week, etc.) in the slicer. And you saved the report. All was right with the world!
But then, tomorrow comes. And all of your slicers still have that “old” date selected, even after you refresh everything. Ick. Who wants to go and update all of those slicers to point to the latest date?
I sure don’t. So, like me, you just let them sit on an old date (or Week, Month, etc.) This forces the consumers of that report to ALWAYS click the latest date, sometimes after scrolling the slicer to the bottom. Every time they open the report. They. Don’t. Like. That. And neither would you.
Given the First Two Columns at Left, How Do We Calculate the Two Columns at Right?
(It’s 1.14 * 1.18 * 1.08 * 1.08… How do we do that?)
I Take No Credit for the Question Or the Answer!
So much coolness and none of it is mine. Today I am merely a messenger.
First of all a great question came in on an old blog post (the one about Moore’s Law and Inflation):
HOW do you perform cumulative inflation calculations in Powerpivot. What seems trivial, multiplying all the values in a column together has me stuck, as there is no PRODUCT() formula. The only way i can think of doing it is through messy iterations but that can’t be it.
Awesome question Andy! And I was stumped. How do we multiply every number in a column together, which is what we need to do here? I mean, if we had a PRODUCTX() function I’d know where to begin.
To the Mother Ship (Microsoft) We Go!
I think the world needs more X functions (I’m serious, that’s not a joke, although it IS a funny thing to say, so I say it often).
So I took this opportunity to ask my former colleagues at Microsoft – can we have a PRODUCTX function in the future? And is there a way to “fake it” in the meantime? Spoiler alert: the answer is yes.
Jeffrey Wang is One Smart Dude
Guest Post by Vivek Gargav
Intro from Rob: continuing the series of guest posts that got temporarily shelved, today we have one from Vivek Gargav. This post captures a trend that I monitor quite closely: people who are heavy users of ERP, CRM, and Accounting systems are increasingly realizing that Power Pivot provides FAR superior reporting and analysis, against those systems, than what those systems provide “in the box.”
It makes perfect sense of course. If you are a software company who produces CRM software, your specialty is… CRM software! Not reporting and analysis software. Furthermore, the needs of your customers couldn’t be more divergent – whatever you build “in the box” is inevitably going to be “lowest common denominator” stuff – borderline relevant to everyone, but not terribly insightful for anyone.
Enter Power Pivot. And people like Vivek Gargav.
ACT! – a Leading CRM System.
Act! is one of the leading Contact Management/CRM products used by many SMBs worldwide within organisations that in some cases don’t have a formal IT department or resource internally. For many, the sole point for reporting on their organisational knowledge is via the weak and complex native Act! reporter, some even use the Dashboard component to extend their reporting capabilities.
Unfortunately neither of these routes provide the depth of reporting requirements that most businesses need (especially aggregate data) and so Act! users traditionally have had to look at 3rd party add-ons including the old stalwart of Crystal Reports.
The Blue Line Smooths Out Random Fluctuations, Tells a Less “Over-Reactive” Trend
I realized recently that this topic has never been covered before, in its most straightforward form, on this site! Actually, it was the subject of a guest post by the esteemed David Churchward, and also by the equally-esteemed Kasper de Jonge, but neither of those posts benefited from the v2 functions available to us today).
To illustrate what we can do with state-of-the-art Power Pivot formulas, let’s start with this simple model: