Creating a KPI in Tabular (DAX)

In this post I will explain how to make a simple KPI in SSAS Tabular. First step is to make two calculated measure in our model. The first one will give us the sales for 01.10.2006. This will be our base measure.

KPI_SalesYesterDay:=Calculate(SUM([Sales Amount]);Filter(Date;'Date'[Date] = DATE ( 2006; 10; 01)))

The second measure defines the target value. That might look like this.

KPI_SalesComparableDay:=Calculate(SUM([Sales Amount]);Filter(Date;'Date'[Date] = DATE ( 2005; 11; 01)))

After creating these two measures you right-click on the measure “KPI_SalesYesterDay” and click “Create KPI”.

This screen will appear

KPI 1

Use the default values. Then deploy the model to the server.

If you create a new Excel pivot it will appear like this:

KPI 2

In a more real life example you might want to use dynamic dates. Like this

KPI_SalesYesterDay:=Calculate(SUM([Sales Amount]);Filter('Date';FORMAT('Date'[Date]; "yyyy-MM-dd") = FORMAT(NOW() - 1; "yyyy-MM-dd")))

This will give us the sales for yesterday. And then subtract 365 days on the target measure to get the comparable day.

KPI_SalesComparableDay:=Calculate(SUM([Sales Amount]);Filter('Date';FORMAT('Date'[Date]; "yyyy-MM-dd") = 
FORMAT(NOW() - 365; "yyyy-MM-dd")))

Or just leave the date filtering to whatever the user chooses

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s