Forum Discussion
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!
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.
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!
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! :)
No bother! It was quite helpfull to look into this and always nice to help a fellow Dutchy out. Good luck!