Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

Need Help
Show and Tell

3 Replies

  • GilesWalker's avatar
    GilesWalker
    Icon for Skilled Sharer rankSkilled Sharer
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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