Monthly change KPI (absolute)

Hi Anonymous

The short answer is: there is no easy KPI that will do this for you. However, even though the DAX language is not quite so intuitive, you could do this with a few lines of DAX code!

I gave it a good thought, and in your case there are maybe two interesting ways to show a nice summed difference:

1. Compare rolling sums. Say you open up your report on November 28th, the report will then use a rolling sum (so calculating e.g. the last 30 day sum) and compare it with previous rolling periods sum (so e.g. the 30 days before this 30 days). In this example, your first rolling sum will include dates 30/10 until 28/11. Then you compare the sum over this dates with the previous period, i.e. 1/10 - 29/10. This works particularly well for a day, week and year-period, but not so well for months (since months have an irregular number of days).

DAX samples to help you out:

TableXSum_last30days = CALCULATE(SUM(TableX[ColumnX),FILTER('Date', 'Date'[Date] >= (TODAY() - 30)))

TableXSum_previous30days = CALCULATE(SUM(TableX[ColumnX]), FILTER('Date', 'Date'[Date] >= (TODAY() - 60) && 'Date'[Date] < (TODAY() - 30)))

Try to set up these calculated measures first and then combine them into a new measure which you can store in a KPI.

2. Compare last months sum & projected sum

First calculate the sum of last month. Then calculate the daily average of this month and multiply by the number of days in the month. Be aware: might provide too high or low values at the start of the month.

DAX sample for previous month sum:

TableXSum_last30days = CALCULATE(SUM(TableX[ColumnX), PREVIOUSMONTH('Date'[Date]))

Hope this helps!