Using Dax to create a KPI

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], 0, MONTH ) ),
        LASTDATE ( DATEADD ( ‘Calendar'[Date], 11, MONTH ) )

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.

Read more on our blog

Get in touch with a P3 team member

  • Hidden
  • Hidden
  • This field is for validation purposes and should be left unchanged.

This field is for validation purposes and should be left unchanged.

Related Content

Speed up your Power BI DAX formulas by up to 95% using the new OPTIMIZEDAX() function

This is an April Fools’ joke. OPTIMIZEDAX() doesn’t exist, but we hope

Read the Blog

Power BI Brain Candy: Value Above Replacement

In today’s article, I’m aiming to achieve three overlapping goals:

Read the Blog

Cube Functions, the good the bad and how to make them great

I’ve written in the past about using the CUBE functions in Excel.

Read the Blog

A different use for Power BI: Self-service refreshes and extracts to Excel while managing security in Power BI

Have you ever needed to get data out to an internal customer

Read the Blog