cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Monthly change KPI (absolute)

Question.PNG

 

 

Dear all,

 

I wish to have a simple KPI with red or green arrows that signal the absolute change between the last month data and the month t-1. If it is up it is green, down red. I have done some time researching options on this forum, but still did not work. In case of the GDP (in Dutch= BBP) it has to be the previous quarter and created the following measure:

 

BBPt-1 = CALCULATE(SUM('00_FEDET'[Bruto binnenlands product]) - PARALLELPERIOD(Kalender[Datum];-1;QUARTER))

 

In the case of Inflation I have monthly data so I created the following measure:

BBPt-1 = CALCULATE(SUM('00_FEDET'[Inflatie]) - PARALLELPERIOD(Kalender[Datum];-1;MONTH))

 

I get the following error:

 

 

 

 

 

 I hope you guys can help me,

 

Cheers

1 ACCEPTED SOLUTION

Accepted Solutions
Fraukje Regular Visitor
Regular Visitor

Re: 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!

 

8 REPLIES 8
Anonymous
Not applicable

Re: Monthly change KPI (absolute)

Error.PNG

Fraukje Regular Visitor
Regular Visitor

Re: Monthly change KPI (absolute)

Hi @Anonymous,

 

I think you should do two sums (to get the two values to substract). At the moment you are only summing the "[Bruto binnenlands product]" column.

Anonymous
Not applicable

Re: Monthly change KPI (absolute)

Error1.PNG

Fraukje Regular Visitor
Regular Visitor

Re: Monthly change KPI (absolute)

Hi @Anonymous,

 

You are still not summing the previous month

 

The DAX should be something like:

CALCULATE(SUM('00_FEDET'[Inflatie]) - CALCULATE(SUM([Total Sales]),PARALLELPERIOD([Date],-1,MONTH))

 

Hope that helps!

Anonymous
Not applicable

Re: Monthly change KPI (absolute)

Hi @Fraukje,

 

Thanks for your help, however I still do not succeed. Let me take it one step backwards, because I think I might make it too difficult for myself while there is an easy option. What would you do if you have GDP data and you want to see whether it has changed from lets say period X to Y and want to know how much it changed and whether it is positive (green) or negative (red). There is got to be a simple KPI visual for this without using DAX.

 

 

Fraukje Regular Visitor
Regular Visitor

Re: 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!

 

Anonymous
Not applicable

Re: Monthly change KPI (absolute)

Wow Fraukje, thanks for the elaborate answer. I will read it more thoroughly when I am not that busy. For now, I succeeded with: InflatieOUD = CALCULATE(SUM('00_FEDET'[Inflatie]);PREVIOUSMONTH(Kalender[Datum]))

 

I don't know what I have changed, I think only a space, which annoys me. However, I think I succeeded because of your help. So thank you very much! Smiley Happy

Fraukje Regular Visitor
Regular Visitor

Re: Monthly change KPI (absolute)

No bother! It was quite helpfull to look into this and always nice to help a fellow Dutchy out. Good luck!