Calculate previous Month number and % difference

[Anonymous]

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

[v-yuezhe-msft]

@Anonymous,

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)

Regards,
Lydia

[GilesWalker]

@Anonymous 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

[Anonymous]

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.