Calculating change in percentage (increase or decrease)

[deankavon]

Hi!

Im new to PowerPivot and havent really even started to use DAX formulas and already i have hit a brick wall :s...

I have the following columns:
-Product
-Sum of A
-Sum of B
-SUM AB
-Type
-Period(date)

I want to create a KPI that will measure the changes in percentage from each previous month with the next or current one.

For example:

In january the sum of product A was 100, in february 80, in march 90--so i want to measure the changes; in february a drop of 20%, then in march an increase of 11% when comparing to the month before.

Im grateful for any assistance!

 

[miguel.escobar]

Hey Deank!

First, you have to start by creating a DATES Tables which is just a table with a column where you have all the dates in a contiguous way and with no "missing" dates. After you create that, you can relate that field to your Period(date) column on the table that you have and start using the time intelligence function.

The one that would do the job would be this one:
'Current Month
=TOTALMTD(SUM(Column that you want to sum), [your dates table here])
'Last Month
=TOTALMTD(SUM(Column that you want to sum), DATEDD([your dates table here],-1,MONTH))

For info about TOTALMTD and DATEADD check this info:
TOTALMTD Function
DATEADD Function


Hope this helps!

 

[deankavon]

Hello Miguel! Thank you so much for the assistance!...

I have already create a date dimension in the separate table. I will try out your instructions. Hope it goes well!

 

[deankavon]

So...when i enter the first formula:TOTALMTD(SUM(DC_TEST[SumOf20]);DimDate[FullDate])in a calculated column it returns the values that are already in my Sum of A column. If i enter the formula beneath the columns it returns: Measure 1: blank.

Again I am lost

 

[miguel.escobar]

Note: I missed one part, when you set up that dates table...you need to put a checkmark on the design tab where it says MARK AS A DATES TABLE.

Can you upload a copy of your workbook so we can take a closer look?
The TOTALMTD should do the trick

 

[deankavon]

I've put the check mark on the design tab.

I cant find the option for uploading the file on the thread :s..you can send me a personal msg so i can send u via email, dropbox etc..

Or am i that dumb of not knowing how to upload a file to this thread?? :s

Thank you for all your effort miguel. i really appreciate it!!!