Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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
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.
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.
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
@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?
@MMartinez
Yes, it's a single formula of monthly percent change per customer per month for "monthly volume" column.
Best Regards,
Herbert
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
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |