- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Monthly change KPI (absolute)

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-27-2017
07:08 AM

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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Fraukje

Regular Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-28-2017
01:22 AM

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)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-27-2017
07:16 AM

Fraukje

Regular Visitor

Re: Monthly change KPI (absolute)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-27-2017
07:20 AM

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)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-27-2017
07:29 AM

Fraukje

Regular Visitor

Re: Monthly change KPI (absolute)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-27-2017
08:06 AM

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)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-27-2017
08:25 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-28-2017
01:22 AM

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)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-28-2017
01:26 AM

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!

Fraukje

Regular Visitor

Re: Monthly change KPI (absolute)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-28-2017
01:40 AM

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