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

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

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

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