Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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!

 

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

Error.PNG

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

Error1.PNG

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

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!

 

Anonymous
Not applicable

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.