skip to Main Content

Dax KPI banner

Not long after I started my career in FP&A, I became interested in KPI development. It’s a natural progression I think: you get the budgeting, forecasting, and reporting under control, and then you begin to dig a little deeper. When I got to this slightly more advanced understanding of FP&A, I started to see the field as more than just a budget template architect and filler-inner.

Every company has a unique set of levers based on what’s essential to it, which is ideally found in its mission and values statements. These levers exist in my daughter’s weekend lemonade stand, multi-billion-dollar Fortune 500 companies, and everything in between. It’s not a question of whether they exist, but how they are recognized, organized, and deployed. Recognition is corporate culture—how is the mission written and how do day-to-day operations fit into it? The organization is the analytic dimension around a KPI. Deployment is how the lever is communicated and reinforced on a regular basis. For this post, my focus is on the organizational aspect.

Recently, I’ve become interested in forecast models. Three months ago, I participated in an FP&A conference. In the pre-conference workshop, I learned that my constant development and generation of revenue and cost forecast templates on a regular calendar was indeed not a forecast model. Instead, a forecast model is the FP&A manifestation of your company’s values (think Visio connecting your business metrics to your company’s values). In my newly framed vision, line managers would manage to KPI’s, not budgets organized by some grouping of accounts that are inaccurate the second that they are finalized.

As this epiphany set in, two thoughts raced through my head.

  1. Oh boy, everything I’ve known and done over the years related to forecasting is wrong (not really, but I was humbled)
  2. I’m so thankful that I have a solid understanding of DAX (and SSAS Tabular)

I can’t think of a more perfect mechanism to develop and organize KPIs. Yes, KPI’s can be developed in financial forecasting software, but you first must have the software and understand the programming involved in creating a KPI. Even then, you need to know how to code efficiently or else you can put a drag on your system’s performance. DAX’s depth is complex, but you can create some powerful stuff just knowing the basics. Adding in SSAS Tabular puts the solution on the enterprise level.

If I can organize a data model effectively and write nice iterative DAX, I believe that, at some point, my whole forecasting model will go from Visio to PowerPivot/Tabular data model. The visual nature of the diagram view and the iterative measure possibilities in PowerPivot/Power BI lend itself well to this approach. We shall see, but in the meantime, I thought I’d show you the guts of a KPI and how DAX can help.

Use Case

Let’s say you manage a project portfolio, which your client funds incrementally. While your company still employs a traditional budgeting and forecasting approach, you have always conducted a stress test after each forecasting cycle to see how well the current level of funding supports your forecast. Recently, that stress test has been formalized into a KPI: funding / projected revenue.

You can download a copy of the workbook to follow along with here.

My data model is relatively straightforward for this example. My two fact tables support the numerator (funding) and denominator (projected revenue). My dimensions support required disaggregation (i.e., by project and time in this example); time also supports required inputs for the denominator.

Here is my data model—dimensions are on the top, facts are on the bottom.

Data model

The connections are 1: many for project and calendar to the revenue table, but I only have a project connection in the funding fact table. This “asymmetry,” for me, usually signals the need to handle the data in the two tables differently through measures. Because the funding numbers are “static,” I either need to make the revenue also static or make the funding number dynamic relative to time. In my example, we’ll do a little bit of both.

Let’s start with the harvest measures:

Revenue Fcst :=
SUM ( Revenue[Revenue Amt] )

Funding :=
SUM ( Funding[Current Funding] )

Now for a little more complexity. At any given date, I want to peg a projection to the next 12 months from that point in time. This is what that measure looks like:

Revenue Fcst Next 12 Mo :=
[Revenue Fcst],
        FIRSTDATE ( DATEADD ( ‘Calendar'[Date], 0MONTH ) ),
        LASTDATE ( DATEADD ( ‘Calendar'[Date], 11MONTH ) )

I won’t go into the guts of this DAX, but it was at least inspired by the GFITW. This is the measure that makes my revenue projection static at any one point in time. It’s easier to validate in the workbook, but you can see that my Jan 2018 projection is the sum of all of 2018, the next 12 months. If I move forward a month, my revenue projection now drops Jan 2018 but adds Jan 2019.

Revenue Fcst Next 12 months

Now I need a measure that calculates the cumulative revenue forecast over time. Why? Because the revenue depletes the funding over time, and I want to know at any one point in time how funded I am against my forecast.

Cumulative Revenue Fcst :=
[Revenue Fcst],
    FILTER (
        ALL ( ‘Calendar'[Date] ),
‘Calendar'[Date] <= MAX ( ‘Calendar'[Date] )

This DAX gets me here:

cumulative revenue fcst

To calculate how much funding remains at any one point in time, I simply subtract two measures I’ve already created:

Funding Remaining :=
[Funding] – [Cumulative Revenue Fcst]

And finally, to calculate my KPI, I again use two measures I’ve already calculated:

Funding % of Revenue Fcst :=
[Funding Remaining] / [Revenue Fcst Next 12 Mo]

Those two measures get me here:

funding remaining

So, as you can see, I have a funding problem at some point in Q4 2018. Whether this is normal or alarming will depend on your business, but I find it fascinating that DAX can do this for me.

Where to go from here

Lastly, I’ll say that KPI’s are not only challenging to develop, but I would recommend you educate yourself on effective KPI development and get yourself ready to invest in trial-and-error. It took me a couple of years to nail down a logical framework just to know where to start. There are all kinds of considerations: data availability, administrative burden to track, who owns it, the frequency of reporting, and the list goes on. It’s also challenging to get people to focus on small, but powerful, sets of KPI’s (like 3-5). And most importantly, it’s challenging to develop meaningful KPI’s that are true levers of your specific business. It’s an art.

Microsoft’s platform is the world’s most fluid & powerful data toolset.  Get the most out of it.

No one knows the Power BI ecosystem better than the folks who started the whole thing – us.

Let us guide your organization through the change required to become a data-oriented culture while squeezing every last drop* of value out of your Microsoft platform investment.

* – we reserve the right to substitute Olympic-sized swimming pools of value in place of “drops” at our discretion.

Matthew Mowrey

Matt Mowrey is the Senior Director of Finance & Analytics at a leading international development consulting services company. He manages FP&A, accounting, and business intelligence for the company’s largest business unit. Matt is particularly enthralled with DAX, M, and SSAS Tabular and uses them to create KPIs, monthly reporting packages, project forecasting tools, predictive financial modeling, U.S. government contractor indirect rate modeling (NICRA), and more. He enjoys traveling and playing board games with his family, and was a Peace Corps volunteer in Russia.

This Post Has 17 Comments
  1. Matthew, Interesting post. Found myself re-reading a couple of times before putting all of the pieces together. A compelling point of view to manage by KPIs instead of just budgets, with a practical DAX case study. Reminds me of what Jeff Sutherland was reaching for in his writings on Scrum.

    Would definitely like to hear more on developing solid KPIs using DAX, Power Pivot and Power BI.

    1. Tom, thanks for the comment. Yes, the no budget approaches are very interesting, but make so much sense to me. When do this, issues reach back into all facets of your business, so it’s a commitment to making the entire mechanism work well. Tons of success stories. If you’re interested to learn more, for starters, check out the book “Beyond Budgeting: How Managers Can Break Free from the Annual Performance Trap.” I could also put you in touch with the group I associate with if you send me a LinkedIn request and a message.

    1. Thank you! See a comment I left above about more info on managing with KPIs. There is a leap of faith–one that I have yet to fully experience–managing to a few powerful KPIs. But the stories once a company gets there are powerful.

  2. Hello, Can anyone explain me(im not project manager) why it’s a problem when cumulative forecasted revenue exceed funding(savings/credit for project)? and then the meaning of kpi showing remaining funding which is negative

    1. Hi Anna. This model was a forecast based on certain variables. If the forward-looking revenue projection exceeds funding, then I don’t have enough funding to continue past the point at which funding turns negative. If I don’t think I’ll receive any more funding, then I have to plan accordingly and adjust my variables and ensure the project is managed accordingly. The other option is to assume more funding. In the model above, I have not assumed more funding, but the model could be used to inform a discussion with the client about timing and amount of new funding. In my industry, projects are incrementally funded to a total ceiling amount. It’s incumbent on us to discuss with our clients the timing and amount of the next tranche, which is where this type of forecasting can be useful (in addition to using it as a forecast stress testing mechanism as I mention above).

Leave a Reply

Your email address will not be published. Required fields are marked *