Monthly percent change per customer per month

[MMartinez]

Hello,

I'm new to Power BI, but I think it's great.  I am trying to calculate monthly percent change per customer per month for three colums (monthly volume, monthly transaction count, monthly avg transaction amount)

I have a column the following columns within my table:

-Customer Name

-Month

-Year

-Monthly Volume

-Monthly Transaction Count

-Monthly Average Transaction Amount

-Month, Year

Any help would be much appreciated.  Thanks.

[MattAllington]

Welcome.  There is a lot to learn. To do this type of calc, you will need a calendar table. Read about that here 

http://exceleratorbi.com.au/power-pivot-calendar-tables/

If if you want to make fast progress, you could consider reading my book http://xbi.com.au/learndax   It will help you learn quickly and efficiently. 

[MMartinez]

I actually ordered your book yesterday and I expect it to to arrive soon.  Hopefully, I'll get to the point were I can figure this out on my own soon.  My data looks like the following and goes from Jan 2015 to Jun 2016:

Customer Name | Month | Year | Monthly Volume | Monthly Trans. Count | Monthly Trans Average

Client 1                  Jan        2015       20,000                 30                                  667

Client 2                  Jan        2015       10,000                 20                                  500

Client 3                  Jan        2015       25,000                 10                                  2,500

Client 1                  Feb       2015       100,000               40                                  2,500

Client 2                  Feb       2015       150,000               70                                  2,143

Client 3                  Feb        2015      75,000                 100                                750

Is adding a calendar table my best option?  If so, what's the DAX formula that will get me the monthly percent change numbers I'm after?  Thanks again for any help.

[v-haibl-msft]

@MMartinez

You’d better to add a calendar table to use the Time Intelligence Functions. Then we can add a new Date column in your table with following formula and change its Data Type to “Date”.

Date = Table1[Month] & " " & Table1[Year]

The next step is to create two new columns in the Calendar table with following formulas. Select YearMonth column and make it sort by YearMonth_Num column as below.

YearMonth = FORMAT( 'Date'[Date], "mmm yyyy" )

YearMonth_Num = YEAR( 'Date'[Date] ) * 100 + MONTH( 'Date'[Date] )

Create a relationship between two tables.

Create two measures with following formulas. We should also be able to get monthly percent change for transaction count and avg transaction amount by replacing the [Monthly Volume] column.

Vol_PreMonth = CALCULATE( SUM( Table1[Monthly Volume] ), PREVIOUSMONTH( 'Date'[Date] ) )

Vol_Monthly%Change = 
VAR Vol_CurrentMonth =
    CALCULATE ( SUM ( Table1[Monthly Volume] ) )
RETURN
    (
        IF (
            NOT ( ISBLANK ( [Vol_PreMonth] ) ),
            DIVIDE ( Vol_CurrentMonth - [Vol_PreMonth], [Vol_PreMonth] )
        )
    )

Best Regards,

Herbert

[MMartinez]

@v-haibl-msft Thank you for the tip.

The last part of what you suggest a bit confusig for me though:

Vol_Montly%Change=

VAR Vol CurrentMonth=

  Calculate(SUM(Table1[Monthly Volume]))

RETURN

   (

        IF  (

              NOT(ISBLANK (Vol_PreMonth)),

              DIVIDE (Vol_CurrentMonth - [Vol_PreMonth], [Vol_PreMonth])

)

)

Is this a single formula to be used in a single measure?

[v-haibl-msft]

@MMartinez

Yes, it's a single formula of monthly percent change per customer per month for "monthly volume" column.

Best Regards,

Herbert

[MMartinez]

http://community.powerbi.com/t5/user/viewprofilepage/user-id/11355

Maybe I'm reading the formula wrong, but I tried entering it as a measure and it wasn't calculating.

Having two = signs, the RETURN, and the VAR is what I don't get.

[v-haibl-msft · Accepted solution]

@MMartinez

We can use VAR to define a variable and it is local to the expression in which we define them. Make sure you don’t miss any bracket.

Best Regards,

Herbert

[MMartinez]

@v-haibl-msft

This is what I needed.  It works.  Thank you for the help.