Forum Discussion

mohany2211's avatar
mohany2211
Frequent Visitor
7 years ago
Solved

Difference between two rows

Hi..........i am trying to calculate the revenue growth between the two dates and based on criteria from another column, but not able to apply the correct formula. can someone help me with a formula to calcuate the same. Here is a small table with the data and expected result

 

thanks in advance for your help 

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi mohany2211,

     

    In Previous Value you can use ABS(PreviousVaue). After return statement in my dax you can change this.

     

    "
    Return
    DIVIDE((CurrentValue-PreviousValue),ABS(PreviousValue)) "

     

    I am also attaching Pbix file with update. Please follow the link Remarks.pbix

     

    Best Regards, 

    Ravi

     

     

6 Replies

  • dobregon's avatar
    dobregon
    Impactful Individual
    7 years ago

    Hi,

     

    I think you need to do 2 things.


    1. Create a column with previous value

    • You need to create a column that has the previous value (previous month as i see in your excel), try something like
      previous value = calculate(value or max (table[revenue]), filter(table, table[date]=date(year(date),month(date)-1,day(date))
      *Try to do this or add another filter

    2. Create the measure revene growth like (revenue - prev revenue) / prev revenue

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      7 years ago

      mohany2211

       

      If you need a  calculated column then

       

      Calc Column =
VAR previousvalue =
    MINX (
        TOPN (
            1,
            FILTER (
                Table1,
                [Remarks] = EARLIER ( [Remarks] )
                    && [Date] < EARLIER ( [Date] )
            ),
            [Date], DESC
        ),
        [Revenue]
    )
RETURN
    IF ( NOT ( ISBLANK ( previousvalue ) ), [Revenue] / previousvalue - 1 )
      • mohany2211's avatar
        mohany2211
        Frequent Visitor
        7 years ago

        Hi Zubair... the formula is working. However, there is one scenario which is giving incorrect result where growth is moving from -ve to +ve between two month than the value is coming as negative. Refer below 

         

        DateEntity Revenue Column
        June 2018BLUE     (10,961) 
        July 2018BLUE     (28,239)157.62%
        August 2018BLUE    107,039-479.05%
        June 2018ABKID    (11,520) 
        July 2018ABKID    (28,797)149.97%
        August 2018ABKID    106,480-469.75%
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi mohany2211,

     

    I have taken your sample data and created a measure for your requirement. its working fine but could you please confirm is this your requirement.

    Note: Please find the PBIX file i have attached here with name Remarks.pbix

     

     

    Best Regards,

    Ravi

    • mohany2211's avatar
      mohany2211
      Frequent Visitor
      7 years ago

      Dear Ravi...thanks for the same. However...my issue is if previous figure is negative and current figure is poistive than also growth is coming as -ve refer table in my previous post.

       

      Can you pls help how to rectify that

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi mohany2211,

         

        In Previous Value you can use ABS(PreviousVaue). After return statement in my dax you can change this.

         

        "
        Return
        DIVIDE((CurrentValue-PreviousValue),ABS(PreviousValue)) "

         

        I am also attaching Pbix file with update. Please follow the link Remarks.pbix

         

        Best Regards, 

        Ravi