cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
srik Regular Visitor
Regular Visitor

Calculate previous Month number and % difference

I have my data as mentioned below. Customer who joined in 2017 Jan which is their Month1 and revenue made in that month. Month 2 revenue made from them and so on.  Same thing Customer who joined in 2017 Feb and revenue made from them in that month which month 1, and next months. I need to calculate difference between % difference between months like (Month2- Month1), (Month3-Month2), (Month4-Month3) and so on. Any idea how to do this? Thanks in advance. 

 

 

 

JoinMonth MonthNumber Revenue

201701       1                       100

201701       2                        80

201701       3                        50

201701       4                        40

201701       5                        38

201701       6                        25

201702       1                        150

201702        2                        70

201702        3                        65

201702        4                        90

201702        5                        50

201702        6                        30

201703        1                        70       

201703        2                        40

201703        3                        30

3 REPLIES 3
GilesWalker Established Member
Established Member

Re: Calculate previous Month number and % difference

@srik I had to change your table structure around slightly as you had no formal date column, so I created one in Power Query based on the origninal data.

 

I then created the following measures:

 

Rev = SUM(Table1[Revenue])

Prior month = CALCULATE([Rev], PREVIOUSMONTH(Table1[Date]))

Difference = DIVIDE([Rev], [Prior month],0)-1

 

Giles

 

 

srik Regular Visitor
Regular Visitor

Re: Calculate previous Month number and % difference

I think this would fail if my month number is more than 12. For Example a customer who joined in 2016-jan and is active untill now his month number would be 20.

Moderator v-yuezhe-msft
Moderator

Re: Calculate previous Month number and % difference

@srik,

Create the following columns in your table and check if you get expected result.

PreviousMonthRevenue = 
var previous= CALCULATE(FIRSTNONBLANK(Table5[Revenue],""),FILTER(Table5,Table5[JoinMonth]=EARLIER(Table5[JoinMonth])&& Table5[MonthNumber]=EARLIER(Table5[MonthNumber])-1))
return
IF(ISBLANK(previous),BLANK(), previous)
%difference = IF(ISBLANK(Table5[PreviousMonthRevenue]),BLANK(),DIVIDE(Table5[Revenue],Table5[PreviousMonthRevenue])-1)


1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.